ADO Recordset for MS SQL Store Procedures

[bradyboyy88]

I have developed the following code to create disconnected recordsets:

'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As ADODB.Recordset
       
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockBatchOptimistic
    
    Sleep 100
    DoEvents
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
    Set oRs = Nothing

My connection is opened in another subroutine and stored as oConn as shown in this route above. Is there a way to do stored procedures with multiple parameters without using the ADODB.Command object? For instance, is there some way to write out my sqlstring variable to initiate a stored procedure?

 

[RoryA]

You can execute SPs as a method of the connection object directly. You could also use a SQL string with EXEC and then the SP details.

 

[bradyboyy88]

You can execute SPs as a method of the connection object directly. You could also use a SQL string with EXEC and then the SP details.

Hey Rory,

Thanks for your help. So you are saying this should work just by making my SQLQuery string a value such as SQLQuery = "EXEC dbo.A1Q_001_LoginA (Username, PAssword)" ? How would I put in the actual values say the username is JohnDoe and the pass is RoryRocks ? Does stored procedures typcally just go in order so its SQLQuery = "EXEC dbo.A1Q_001_LoginA ('JoeDoe', 'RoryRocks')"?

'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As ADODB.Recordset
   
 
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockBatchOptimistic
  
    Sleep 100
    DoEvents
  
    'Open Record Set by executing SQL
    'Say SQLQuery = "EXEC dbo.A1Q_001_LoginA ('JoeDoe', 'RoryRocks')"
    oRs.Open SQLQuery, oConn
  
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
      
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
  
    Set oRs = Nothing

End Function

Also, for completeness purposes is this what you are referring to about doing the command directly against the connection itself? I still have to declare the command objects to do that right? Also, I have to type out all the info for the parameters such as is it a integer, string, etc. doing this way. Anyway around that? I am trying to create a more universal function that executes these stored procedures and the parameters may vary from strings, integer, etc. Using my approach below DOCmd.CreateParameter(sParmName, adInteger, adParamOutput) assumes an integer but I could not find a universal type for these constraints so this is the issue with this approach for me unless I am misunderstanding. Again thanks so much. I am moving a access db to these stored ms sql database stored procedures so trying to be as universal as possible.

'Return a stored procedure as a recordset
Public Function SQLStoreProcedureDatabaseRecordset(StoreProcedureName As String, Parameters As Scripting.Dictionary) As ADODB.Recordset
  
    Dim sParmName As String
    Dim ADOCmd As ADODB.Command
    Dim ADOPrm As ADODB.Parameter

    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockBatchOptimistic
  
    Set ADOCmd.ActiveConnection = oConn
  
    With ADOCmd
        .CommandType = adCmdStoredProc
        .CommandText = StoreProcedureName
    End With
  
  
    'loop through parameters
    For Each key In Parameters.Keys
        sParmName = key
        Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
        adParamOutput)
        ADOCmd.Parameters.Append ADOPrm
        ADOCmd.Parameters(sParmName).value = Parameters(key)
    Next
  
    Set oRs = ADOCmd.Execute
  
    'Open Record Set by executing SQL
    oRs.ActiveConnection
  
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
    Set ADOCmd.ActiveConnection = Nothing
  
    'Return disconnected recordset
    Set SQLStoreProcedureDatabaseRecordset = oRs
  
    Set oRs = Nothing
  
End Function

 

[bradyboyy88]

Trying the short command above by putting it in the SQLString parameter it creates a error saying incorrect syntax which I am guessing is my exec command. So I must be wrong in that being the syntax. Curious to see what your reply is!

 

[RoryA]

It's been a while since I've needed to do this, but I think you need to remove the brackets:

SQLQuery = "EXEC dbo.A1Q_001_LoginA 'JoeDoe', 'RoryRocks'"

 

[bradyboyy88]

It's been a while since I've needed to do this, but I think you need to remove the brackets:

SQLQuery = "EXEC dbo.A1Q_001_LoginA 'JoeDoe', 'RoryRocks'"

Hey so your syntax worked! However I get an error saying :

The request for procedure 'A1Q_001_LoginA' failed because 'A1Q_001_LoginA' is a table valued function object. Any I know I am returning a table but how do i return that to a recordset? I feel like i am so close ha!

My stored procedure is :

CREATE FUNCTION dbo.A1Q_001_LoginA (@A2Username as varchar(255), @A2Password as varchar(255) )
Returns table
Return (

Select * FROM dbo.USER_ACCOUNTS WHERE USERNAME=@A2Username AND PASSWORD=@A2Password

)

 

[Kyle123]

You don’t have a stored procedure, you’ve got a table valued function, you’d use it as a normal select query with the record set as you were initially, the syntax is:

select * from A1Q_001_LoginA(‘username’, ‘password’)