Help getting value back from Stored Procedure

[Ira]

Hello,

I have a MS ACCESS function:

Function GetOrderCount(ComputerName As String) As Integer
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ParameterString As String

Set cnn = New ADODB.Connection
cnn.ConnectionString = "ODBC;DescXXXXXXxxxxxxhem"
cnn.Open
Set rs = New ADODB.Recordset

Set rs = cnn.Execute("Exec dbo.CountOfOrdersInQueue ‘Kevin’ ")

MsgBox rs![MyReturn]

That calls this SQL Server stored procedure:


ALTER PROCEDURE [dbo].[CountOfOrdersInQueue]
@ComputerName nvarchar(25)

AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM ORDERS WHERE [FlagforExport] = 1 AND [Shipped]= 0 AND [ComputerToPrintThisOrder]= @ComputerName


SELECT @@rowcount AS MyReturn
END

When I run it using Server Management Studio, it works, but I can't get a result in Code. Any help?

[June7]

What does "can't get a result in Code" mean - error message, wrong result, nothing happens?

Why not link to table?

Could you use a Pass-Through query? That would require modifying the SQL statement in query object.

I finally got SP to work. SQL you posted is not to execute the procedure but to modify it. Opening that caused me so many headaches. Right click on procedure, click Execute Stored Procedure then Okay - this opens a window with executable code, click Execute and results show below code. I had good luck with Count(*) instead of @@rowcount which returned wrong value.

Since you only want a single value, don't really need recordset. Finally got this VBA method to work https://stackoverflow.com/questions/...dure-in-access

Also finally got recordset version to work but don't see really needed in this case.


NOTE: Please post code between CODE tags to improve readability.

[jojowhite]

you can also use DAO method.
if ORDER table is already a Linked Table:

Public Function GetOrderCount(Byval ComputerName As String) As Long
    GetOrderCount = DCount("1","Order", "ComputerToPrintThisOrder = '" & ComputerName & "' And FlagForExport = 1 And Shipped = 0")
End Function

[Gicu]

That would be a domain aggregate function not DAO....
Cheers,

[NoellaG]

The SQL procedure @@Rowcount, this is an int (Access long), not a recordset.

[madpiet]

Well, for starters, if you want to return a scalar value, use a Function and not a Stored Procedure.

[NoellaG]

I wouldn't advise to use SQL functions, stored procs are more flexible, and a function can return only 1 value, a sp can return several values. I don't think I have used functions in the last 20 years I worked with SQL server.

[madpiet]

like this? (yeah, just a dumb query I had sitting around)

SELECT @@ROWCOUNT
FROM
(SELECT PurchaseDate, IngredientID, UnitPrice, PrevPrice = LAG(UnitPrice,1) OVER (PARTITION BY IngredientID ORDER BY PurchaseDate)
FROM Purchases) p
WHERE p.PrevPrice IS NOT NULL;

[NoellaG]

original query was on table ORDERS with criteria on [FlagforExport], [Shipped] and [ComputerToPrintThisOrder]?

[Minty]

I've used table valued functions within SP's, that works really well, but there is a time and a place.

I'm currently building a function to return price increases based on varying time spans, and I can't build it into a query. (or I'm not clever enough to make it work!)
It will have to be a function (or a TVF) as I need to reproduce the answers over large dataset.

[NoellaG]

Hi Minty

I can't build it into a query

Have you tried a procedure using temp tables? Create them, fill them and then add the necessary indexes. Base your final result on a query on the temp tables and don't forget to drop them. Nearly all our reports data sets are defined through SP's.
If you can't do it in a SP, you won't be able to do so in a function.

[Minty]

I use temporary tables extensively in SP's. They are so useful it's not true.
When combined with windowing functions to add meaningful row numbers and sort orders, they are an incredibly powerful way of manoeuvring data around and efficient as they make it much easier to use Set based processing.

The table valued function is really useful within an SP as it can be called multiple times without resorting to dynamic SQL.
You probably could use a temp table as well, but in one application I have to return about 7 or 8 values from a source piece of data, and the calculations get repeated with different values as the procedure processes.

[NoellaG]

I hope you're doing the work on an asynchronous node, or your DBA might be sitting in a corner silently weeping

[Minty]

It's not a process that gets run very often, thankfully.

[madpiet]

I've used table valued functions within SP's, that works really well, but there is a time and a place.

I'm currently building a function to return price increases based on varying time spans, and I can't build it into a query. (or I'm not clever enough to make it work!)
It will have to be a function (or a TVF) as I need to reproduce the answers over large dataset.

Funny you should say that... How are you identifying the price increases? I'm assuming you're talking about SQL Server????? (maybe not, given that this is an Access site)

LAG() with a window is your friend.

(could you post your table definition?)