Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Using a Variable to Call a Stored Procedure Name

    Hello;

    I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.

    I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.

    So how do I refer to a procedures name using a variable?

    Here's my code;


    Function LieuBen()

    MyCalendar = CurrTSCalendar

    Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
    Call PopulateTmpFile("sp_PopTmpCalcLieuBen")

    End Function

    Function PopulateTmpFile(MySProc As Variant)
    Dim sp_PopulateTempOTTable As String

    Const DS = "SOS-1"
    Const db = "TIMS"
    Const DP = "SQLOLEDB"

    Dim objConn As New ADODB.Connection
    Dim objRs As New ADODB.Recordset
    Dim objComm As New ADODB.Command

    ConnectionString = "Provider=" & DP & _
    ";Data Source=" & DS & _
    ";Initial Catalog=" & db & _
    ";Integrated Security=SSPI;"

    ' Connect to the data source.
    objConn.Open ConnectionString

    ' Set a stored procedure
    objComm.CommandText = MySProc
    objComm.CommandType = adCmdStoredProc
    Set objComm.ActiveConnection = objConn

    objConn.MySProc MyCalendar, objRs

    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objComm = Nothing


    End Function

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    First, I'm not necessarily certain that what you are doing is a good idea. I certainly wouldn't view it as a "best practice". If you have two different stored procs that serve different purposes, then you probably ought to call them from different functions. Alternatively, you could potentially create a single stored proc with a flag value that would execute differently based on the value in the flag.

    In addition, I was puzzled by this line in your original post:
    My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
    That all being said, I was able to write a quick function that called a stored proc based on a variable.

    Here is the code:
    Code:
    Dim sName
    
    sName = "spMySproc"
    
    Call CallSproc (sName)
    
    Function CallSproc (sSprocName)
    	Dim oComm
    	Dim oConn
    	Dim oRS
    	Dim sConnectionString
    	Dim i
    	
    	Set oConn = CreateObject("ADODB.Connection")
    	Set oComm = CreateObject("ADODB.Command")
    	Set oRS = CreateObject("ADODB.Recordset")
    	
    	sConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CATALOG;Data Source=MyServer"
    	oConn.ConnectionString = sConnectionString
    	oConn.Open
    	
    	oComm.CommandText = sSprocName
    	oComm.CommandType = 4 ' adStoredProc
    	oComm.ActiveConnection = oConn
    	Set oRS = oComm.Execute
    	i = 0
    	While Not oRS.EOF
    		oRS.MoveNext
    		i = i + 1
    	Wend	
    	MsgBox i
    	
    End Function
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •