Example of SQL Server Stored Procedure which does SELECT, ADO Command / Parameters?

[mdlueck]

I am using Stored Procedures for all INSERT / UPDATE / TRUNCATE operations. The Access / VBA side of those use ADO Command / Parameters objects. Code is as shown here:

http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

I have seen posts suggesting that it is possible to place a SQL SELECT into a Stored Procedure, pass the search criteria into the SP via ADO Parameters objects.

It is not clear to me how the selected columns would map to ADO.Parameters objects, and how to deal with the result set having more than one record... how to step through the result set one record at a time.

I have been banging into the upper limit of string concatenation building pass-through SQL SELECT queries. So thinking to research transitioning SQL SELECT queries into Stored Procedures before I hit the wall of too long of a SQL SELECT query.

 

[tehNellie]

There is (was?) a system stored procedure designed to receive and execute SQL as a parameter it's name eludes me off the top of my head, but if you're going to pass an entire SELECT statement to it, why try and then also pass parameters and then set them? The SQL is in the application so you might as well set the "parameters" there and then surely? Or did I misunderstand?

You can just open the results of a stored procedure into a recordset, basically as you would an Access query.

Another option might be to use a view which you could then use as a linked table or query via a passthrough but taking some of the donkey work out of Access/your application and handing it off to SQL server.

i.e. instead of

SELECT 100s of columns
FROM thisTable
LOTS OF JOINS
WHERE
Stacks of criteria

Dump the basic query and as many of the WHERE clauses as are static into a view and set your passthrough query to use the view instead so you just have

SELECT Columns
FROM theview
WHERE Access supplied clauses

This should also give you the advantage of the basic view query having a query plan on the server

 

[pbaldy]

This is my template code to open a recordset on a stored procedure. Is that what you're after (I use a public variable for the connection)?

  Dim rst              As ADODB.Recordset

  EstablishConnection

  Set rst = New ADODB.Recordset
  rst.ActiveConnection = objConn

  rst.Open "EXEC ProcName '" & Me.Whatever & "'"

  Set rst = Nothing
  ReleaseConnection

Personally I use the ADO command method for action queries, this method for recordsets.

 

[mdlueck]

Personally I use the ADO command method for action queries, this method for recordsets.

This I think is the main point of my disconnect. I fail to see how wrapping a SELECT statement in a Stored Procedure wrapper would work.

ADO.Command objects are what can use ADO.Parameters objects, not record set objects. So using that scenario I would not be able to wrap each parameter value inside an ADO.Parameters object.

 

[mdlueck]

What I envisioned as I had seen mentioned "SELECT in a Stored Procedure" I would think means...

1) ADO.Command object runs the Stored Proc
2) Refresh of Parameters obtains the list of parameter args to be passed into the Stored Procedure.
3) So populate those Parameters, then Execute
4) Output delivered via more Parameters objects
5) And how to find out when you have reached the end of the result set? (shrug)

 

[tehNellie]

You've lost me a little on this, there's no functional difference between a stored procedure that runs an INSERT statement and one that runs a SELECT statement.

http://support.microsoft.com/kb/174223

Example using command.parameters.refresh on a select query using an input parameter, output parameter and return parameter. You don't need to run the SP to use the parameters.refresh

The query in that example just returns an output parameter but it could just as easily be a dataset.

 

[mdlueck]

http://support.microsoft.com/kb/174223

Aaahh, their example obtains an adoRS object upon running the Stored Procedure. That makes more sense.

I will do some more thinking on this concept. Thanks!

 

[tehNellie]

Pbaldy's solution works just as well, as with everything, there's more than one way to skin a cat.

I tend to use a similar method to yourself regardless of what the SP is doing.

 

[mdlueck]

Pbaldy's solution...

Except ADO.Parameters do not work with ADO.Recordset objects. I really enjoy leveraging Parameters objects.

 

[mdlueck]

Today I had success with both ADO and DAO objects executing Stored Procedures which perform a SQL SELECT.

Following is a thread which I started trying to figure out a way to replace the SQL Pass-Through query inside of a DAO.QueryDef object:
"Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's"
http://www.access-programmers.co.uk/forums/showthread.php?t=223414

And swapping out the pass-through SQL for SP's where adoCMD objects are used worked slick. Indeed, the result of executing a SP with an adoCMD object is an adoRS object as my existing code was already coded to expect.

All in all, a very smooth migration for four monster-large SQL queries.

 

[Rx_]

Not a solution for every occasion, but will throw this out there. A page of SQL text was custom assembled using vba. Huge amount of joins. The big IF is: if there is an opportunity to use pass a query through once, compile the pass-through query once, then hit the new passthrough several times with different sub-views.

300 Set qdefPT = CurrentDb.QueryDefs("qryPT_WorkingCapital_Position")
310 qdefPT.SQL = strPT ' the sql string is a page full - not shown
320 qdefPT.Connect = strODBCPTConnect ' a global ODBC connect string
330 qdefPT.Close

A short time later run many SQL Select statements as a dbOpenSnapshot against the qryPT_WrokingCapital_Position.
strSQL = Select (field lists) FROM qryPTWorkingCapital_Position.....
370 Set rsExport = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Once the big query was constructed for that day's trading range, and passed through to SQL Server, the field list out of that query on SQL might be run many times with different field list.
I can now save this query and use it as the record source for an Access form or report. Essentially, I can use this query as if it is native to the Access database.

This is my favorite step-by-step for anyone trying a pass-through the first time. http://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/