Sprocs

[space_monkey]

Ok i've created my first stored procedure and i have a couple questions. Don't laugh.

Here's my sproc.

USE MWDB
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSERT_ACCT_TAGS]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[INSERT_ACCT_TAGS]

GO

CREATE PROCEDURE INSERT_ACCT_TAGS
	 @ORDR_NUM varchar(20)
	 ,@LINE_NUM varchar(20)
	 ,@TRAK_NUM varchar(20)
	 ,@DATE_TIM datetime
	 ,@USER_NAM varchar(20)
         ,@PROD_LOC varchar(20)
AS

Set NoCount On

Insert into ACCT_TAGS (ACCT_TAGS_ORDR, ACCT_TAGS_LINE, ACCT_TAGS_TRAK, ACCT_TAGS_DATE,
ACCT_TAGS_USER, ACCT_TAGS_PROD) Values (@ORDR_NUM, @LINE_NUM, @TRAK_NUM, @DATE_TIM, @USER_NAM, @PROD_LOC)
Go

GRANT EXECUTE ON INSERT_ACCT_TAGS TO SPROC_USER
Go
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

It's simple, but that's usually a good place to start. Now my questions are...
1. How do i use this in a vb program?
2. How do you all handle user permissions with this? We use windows authentication right now, usually putting each person in a role so that they all have the right abilities.

[szlamany]

Nice - I recognize that formatting!

Answer to #2 first - SPROC_USER is a ROLE - right?

If so you assign that role to either all the WINDOWS users or to WINDOWS GROUPS that they might already be in. That depends on how you AD is setup.

[szlamany]

Answer to #1...

Check out this thread:

http://www.vbforums.com/showthread.p...&highlight=gcn

also - search the forum for GCN - that's my standard connection object - you will find lots of threads on this...

[space_monkey]

Yeah i pulled most of that sproc from your tutorial for kleinma.

Here's what i have in my vb function.

Private Sub INSERT_ACCT_TAGS(ByVal strOrderNum As String, ByVal strLineNum As String, ByVal strTrackNum As String, ByVal strUserName As String, ByVal strProdLoc As String)
Dim adocmd As ADODB.Command
Dim MS_SQL_FUNCS As SQL_SERVER_CONNECT

Set adocmd = New ADODB.Command
Set MS_SQL_FUNCS = New SQL_SERVER_CONNECT

If MS_SQL_FUNCS.OpenDbCon = False Then
    Exit Sub
End If

    With adocmd
         .CommandText = "INSERT_ACCT_TAGS"
         .CommandType = adCmdStoredProc
         .ActiveConnection = MS_SQL_FUNCS.strCon
         .Parameters.Append .CreateParameter("@ORDR_NUM", adVarChar, adParamInput, 20, strOrderNum)
         .Parameters.Append .CreateParameter("@LINE_NUM", adVarChar, adParamInput, 20, strLineNum)
         .Parameters.Append .CreateParameter("@TRAK_NUM", adVarChar, adParamInput, 20, strTrackNum)
         .Parameters.Append .CreateParameter("@DATE_TIM", adDBTimeStamp, adParamInput, 8, Now)
         .Parameters.Append .CreateParameter("@USER_NAM", adVarChar, adParamInput, 20, strUserName)
         .Parameters.Append .CreateParameter("@PROD_LOC", adVarChar, adParamInput, 20, strProdLoc)
         .Execute
    End With

MS_SQL_FUNCS.CloseDBcon


End Sub

For some reason I'm getting an error that I'm passing more arguments than i'm supposed too.

Edit: well it just started running.... so I dunno.

[space_monkey]

Oh i forgot. The SPROC_USER is defined as a DB user right now. But i'll check out using that as a role.