SQL stored Procedure Parameters not sent by name

[Kenny_K]

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.

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).

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

[tst]

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.

[aputman]

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]

I've posted examples here and here

[terickson]

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

[Kenny_K]

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.