MS-SQL and Stored Procedures Options

[Cobblestone]

Hi,

Thank you for a great and easy toolkit.
However I have a question about stored procedures.

How do you call an MS-SQL stored procedure which returns parameters and/or a return code ?

I did some trials and I can execute stored procedures which return a complete dataset (simple SELECT statement in the stored procedure)
But could not get a procedure to work when I have to add parameters or get parameters .

Thanks for any help

[CB]

Hi,

Thank you for a great and easy toolkit.
However I have a question about stored procedures.

How do you call an MS-SQL stored procedure which returns parameters and/or a return code ?

I did some trials and I can execute stored procedures which return a complete dataset (simple SELECT statement in the stored procedure)
But could not get a procedure to work when I have to add parameters or get parameters .

Thanks for any help

hi, and thank you for your appreciation.

currently the ADO-Toolkit does NOT support return parameters in Stored Procedues, but only the "normal" return values, which come e.g. from a Select Statement in the Stored Procedure.

My workaround is, to give return parameters back to the ADO/ODBC through a select statement, so I get a table with one line and n columns back, which I can easily parse and convert to LabVIEW data.

Example:

My Stored Procedure "SP_testProc" expects 3 input parameters, int A, tinyint B and String C and gives Back smallint X, int Y, bool Z

the call to the SP is: EXCEL SP_testProc @A=1234, @B=1, @C='whatever'

the last statement in my Stored Procedure is

SELECT @X as foo, @Y as bar, @Z as hurg

BTW: make sure you use the EXEC VI which expects a return table, if the SP is giving back data. There is one which just executes the SP and does not collect the data after the execution. It is a good practice anyway to use the instance that collects the return values from the stored procedures (even if you just give back a "SELECT 1" if you don't want to give back data), because this instance waits for the SP to be finished, while the other one does not!

[Cobblestone]

At first it didn't work but after some trials everything works now.

If I use as last statement in my sp
SELECT @@IDENTITY
to get the key of the last added record I get an empty variant although this works in the MS Query Analyzer

If I use as
DECLARE @MyData int
SELECT @MyData = @@IDENTITY
no luck either

If I use
DECLARE @Mydata int
SET @MyData = @@IDENTITY
SELECT @MyData
Then everything works

I still have to try other parameters but I think I have it figured out now.
Thank you for the quick response