MS-SQL and Stored Procedures |
MS-SQL and Stored Procedures |
19. May 2008, 15:04
Post
#1
|
|
new Member Group: Members Posts: 2 Joined: 19.05.2008 Member No.: 58 LV Version: 8.5 Zertifizierung: keine |
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 |
|
|
19. May 2008, 16:09
Post
#2
|
|
proven Member Group: Administrators Posts: 315 Joined: 16.10.2006 From: Düsseldorf Member No.: 2 LV Version: current Zertifizierung: CLA LV User seit: 2001 |
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! -------------------- künstliche Intelligenz ist besser als natürliche Dummheit!
rotabench:rotierende Prüfstände nach dem Baukasten-Prinzip |
|
|
20. May 2008, 07:43
Post
#3
|
|
new Member Group: Members Posts: 2 Joined: 19.05.2008 Member No.: 58 LV Version: 8.5 Zertifizierung: keine |
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 |
|
|
Lo-Fi Version | Time is now: 06.08.2024 - 02:48 |