Database Store Procedure as Query to Get Return Value

[Charlie0071]

From following website, 

https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z000000PA94SAG&l=en-US
Return Values of Stored Procedures using Database Connectivity Toolkit

The stored procedure is:

CREATE PROCEDURE dbo.storedprocedure
@theVariable [nvarchar](10) OUTPUT
AS
set @theVariable = 'return me'
RETURN

it works as following:

Then, I would like to execute following query to get return value:

Use LeakDAS
DECLARE @theVariable [nvarchar](10);

EXEC dbo.storedprocedure
@theVariable ='hello'
set @theVariable = 'how are yo'

SELECT 'return value'=@theVariable

However, I don't have lucky. Here is my code:

SQL Query 2 has above query. There is no error message. However, Value2 are always Empty. 

Is anything wrong? or is it impossible by doing this way to get returned value?

[Mark_Yedinak]

You need to call the "DB Tools Fetch Recordset Data.vi" after your call to the execute. At the moment you never actually attempt to read the data.

[Charlie0071]

I added it. Now it looks like:

and result likes:

and still no returned value. 

Right now, the stored procedure is called by a query. and this query has a returned value. I can not find any example about how to achieve it. However, in the tool set, there are many function to DB Tools Get Parameter Value VI, integer, string, double....

[Mark_Yedinak]

Pass the correct SQL into the Execute VI.

[DavidBoyd]

I'm not going to try very hard to debug your code, sorry, but I am going to try to offer you a generalized procedure to properly invoke a true stored procedure. In my experience, an SP is NOT some random SQL you throw at your server on the fly, it is a durable item you declare in your database management system, and then use the DB toolkit to create a parameterized query interface that is prepared by procedure name, and passes named, typed, parameters to it, executes it, collects any recordset(s) it may generate, then reads back parameters assigned as OUTPUT, and/or the RETURN variable.

The attached VI diagram is fairly lengthy, but I urge you to look at the extensive comment block.

I'll follow up later if you get stuck.

Dave

I 

[Charlie0071]

Thanks for Dave. It looks like there is no way to get return value from a query. Stored Procedure example says: No parameter or Has Parameter. If return value as parameter, it needs parameterized query. 

My solution is let the query return some data set, and judges it from there instead of return value as one output parameter.  

It looks like work.  

[DavidBoyd]

Charlie0071,

Every time someone says "there is no way to do that"... ...in a general posting about LabVIEW, or about the Database Connectivity toolkit in this instance, they are usually wrong, there IS a way.

I struggle a little with some of your English descriptions, and that's no one's fault; I thank you for using your English language skills to post on this forum.  I believe what I understand is that you feel you can only obtain the scalar value you want by returning it as a recordset (via a SELECT statement).  This is not true; the toolkit DOES permit the programmer to retrieve scalar parameters and/or a procedure return value.

A procedure definition ends with a RETURN statement, but your examples I've seen do not include a variable as part of that return.  As an example, if you want to return the value of @MyIntVariable, you need to end the procedure definition with

RETURN @MyIntVariable

If you read the comments on that block diagram for a generalized flow of using the toolkit for a procedure call, you will see that you:

1) start with a Connection reference;

2) when you prepare a procedure, you get a Command reference;

3) you use the Command reference to set any parameter values you are passing TO the procedure;

4) when you execute the procedure, you get a Command-Recordset reference.

5) you MUST fetch recordset(s) the procedure generates, if any (it typically does so via a SELECT statement), at the Command-Recordset level.

6) then free the Command-Recordset reference; you get back to the Command reference.

7) you may now retrieve the parameters from the Command reference - you may do so by index or by name.  The only ones the procedure might have modified are those which are defined by the procedure as OUTPUT (SQL Server also allows INPUT/OUTPUT), and RETURN.

The RETURN parameter (there can be at most one) is specified by index 0, or the canonical name @RETURN_VALUE.

😎 when done retrieving any modified parameters, free the Command reference; you get back to the Connection reference.

You have not created a stored procedure in your database by sending SQL text defining a PROCEDURE.  That defeats the purpose of SQL's ability to have persistent stored procedures.  Created within the database, stored procedures are compiled and optimized, and have an execution plan.  When you send SQL text from LabVIEW to your database server (as you're doing), it must be interpreted on the fly.  For trivial examples such as the ones you've provided, the efficiency gains are likewise trivial.  But for real-world examples, where an application needs to move substantial data to/from the server, and especially if that server is remote, and the round-trip time is considerable... the stored procedure/parameterized query approach is far more efficient.

Also, SQL commands on-the-fly are less secure - unless you absolutely control the textual content of values you specify, they are vulnerable to SQL injection attacks.  It might seem unlikely in your context, but anyone reviewing your database solution for security will call this out.

What database backend ARE you using, by the way?  Did I miss where you mentioned that?

Best regards,

Dave

[Charlie0071]

Dave, 

I got the point.

For my case, I have 30 parameters stored procedure. It terrified most people for use Command reference method. That's why I want to avoid it. Plus, text query is easy to test. 

I added:

SELECT @equipment_id_next as N'@equipment_id_next',
@operation_code_next as N'@operation_code_next'
@return_value as N'@return_value'

as statement after:

EXEC @return_value = [dbo].[plc_xxx]

....

Then, return_value is included as dataset.

and then, one simple query make labview code much more easy. Plus, if the insert statement is not successful, then, I can save them to text file and ask operation to load this file to execute queries.

Any way, parameterized queries in labview is powerful and it needs some time to get it. However, simply query method make life a little bit easier. That's why I am looking for it. 

Thanks. It is resolved. 

[marcus.anderson]

What are these VI's from Dave's earlier post and where do I find them? They are not on my Database connectivity pallet (LabVIEW 2023). 

[DavidBoyd]

That lengthy example from the post in July 2021 was a snippet created in LV2019.  The bit you zoomed in on includes an OpenG VI (in general, any VI you find with that pale green coloring is likely out of the OpenG suite somewhere), specifically the Set Data Name VI from the OpenG Variant Data package.  The other is the Cmd Get Parameter Value VI from the DB toolkit, but it's the lower-level version that returns the data as an OLE variant, not one of the upper-level polymorphic instances which convert the variant to a specific LV type.

Sorry for the confusion this caused.  If you'd prefer, I could provide you with a substantially identical Execute Stored Procedure which I reworked to remove the OpenG dependency you're missing. (I've substituted NI's own Set Type Information VI from the palette Cluster, Class, and Variant->Variant->Data Type Parsing, though it requires a little bit of extra code to assign the name AND preserve the variant value.)

Dave