ADO Recordset for MS SQL Store Procedures

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
I have developed the following code to create disconnected recordsets:

Code:
'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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 25, 2015
Messages
562
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')"?

Code:
'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.

Code:
'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

Well-known Member
Joined
Feb 25, 2015
Messages
562
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

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

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
It's been a while since I've needed to do this, but I think you need to remove the brackets:

Code:
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 :

Code:
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,744
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’)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,479
Messages
5,831,915
Members
430,091
Latest member
Generally_confused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top