Help - Search - Members - Calendar
Full Version: MS-SQL and Stored Procedures
IBB Forum > IBB Support > ADO-Toolkit
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
QUOTE(Cobblestone @ 19. May 2008, 16:04) *
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2024 Invision Power Services, Inc.