LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

SQL stored Procedure Parameters not sent by name

My co-workers and I were troubleshooting a stored procedure today, and we found that it seems like the parameters do not seem to be updated and linked to the variables in the SP.

 

I have been using the standard syntax (http://digital.ni.com/public.nsf/allkb/07FD130746083E0686257300006326C4), {call SP_NAME(?,?,?)}, and entering the parameters into the parameratized query vi.

 

I have also tried the Stored Procedure Syntax and changing the Stored Procedure? input to True (pictured below).

 

 

Say you have a Stored Procedure where one of the values is optional. (Param1 is optional since it is already at to NULL and is handled in the SP properly.  We can execute the SP in SQL Management Studio just fine like this.).

 

I am providing 3 paramters - @Param2, @Param3, @Param4 via my code.

 

Labview database code 2.gif

 

 

ALTER PROCEDURE [dbo].[SP_NAME] @Param1 int = null,@Param2 varcahr(20),@Param3 int,@Param4 int
AS
BEGIN

** Rest of Stored Procedure**

 

 

But when you run this in LabVIEW, I get the following error:

 

Previous.vi<ERR>ADO Error: 0x80040E10
Exception occured in Microsoft OLE DB Provider for SQL Server: Procedure or function 'SP_NAME' expects parameter '@Param4', which was not supplied. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi

 

So it seems like the code is setting by the order of the inputs to the SP instead of by the Parameter name (since Param4 is the 4 th parameter and I only supply 3).

 

I also tried the Set Parameter Values vi, but that did not make a difference (see below).

 

Labview database code.gif

 

Am I mis-reading how the vi's are supposed to work?

 

 

Kenny

0 Kudos
Message 1 of 6
(1,383 Views)

I don't know the answer (although if you want to find out, I would look at why there's a coercion on the input to the VI), but I can say that I usually just call SPs with the standard SQL call (which I believe is "exec SP_Name param1,param2,param3", although you can probably also pass the names) and I would suggest considering doing the same. You can fit the values into the string using the Format Into String primitive.


___________________
Try to take over the world!
0 Kudos
Message 2 of 6
(1,369 Views)

Yes, you are correct.  It works like any function that you might write in C/C++.  Names of the parameters do not matter.  You will need to supply an empty parameter in position 0 of the array in order for it to work properly.

 

You also do not need to call the Set Parameter VI since you have already set the parameters when you called Create Parameterized Query.  Also, your query text should only be the name of the stored procedure. 

 

There is a bug related to this and I'm not sure if it has been fixed yet or not.  The bug ID is in the following post and I gave a work-around that works for me (Labview 2012). 

 

http://forums.ni.com/t5/LabVIEW/Calling-stored-procedures-with-parameters-with-the-Database/td-p/276...

aputman
LabVIEW 2017
LabVIEW Programming
0 Kudos
Message 3 of 6
(1,357 Views)

I've posted examples here and here

aputman
LabVIEW 2017
LabVIEW Programming
0 Kudos
Message 4 of 6
(1,355 Views)

Use this syntax when executing a stored procedure in LabVIEW

 

name_of_stored_procedure
@parameter_a_name = value,
@parameter_b_name = value,
@parameter_c_name = value

 

In the stored procedure, any parameters not supplied when called must be assigned a default value when they are declared so that the stored procedure can set their value.  Like this:

 

@parameter_d_name varchar(50) = 'some default value'

@parameter_e_name float = 100

 

 

 

CLA
0 Kudos
Message 5 of 6
(1,330 Views)

I was able to get it working by using the following string:

 

exec SP_NAME @Param2 =999999,@Param3 = 1,@Param4 =1

 

As noted before, Param1 is get to optional in the Stored Procedure.

 

I did not have to wire anything to the "Execute Query.vi" parameters input.

Kenny

0 Kudos
Message 6 of 6
(1,310 Views)