Assistance with DB Tools Create Parameterized Query and SQL Server SP

[Eric1977]

All,

I could use some assistance retrieving data from a stored procedure. Based on what I’ve read and reviewed in the Knowledge Base, everything appears to be configured correctly, but I’d appreciate another set of eyes on the VI to help identify what I may be missing. This is my first time retrieving data from SQL Server using a stored procedure.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Eric Brenner
-- Create date: Janualy 22, 2026
-- Description: Checks to see if the PC Name
-- is a valid data analysis computer.
-- =============================================

ALTER PROCEDURE [dbo].[uspGetPlantProductList_Select]
@PC_NAME NVARCHAR(50),
@Exists BIT OUTPUT,
@PLANT_CODE NVARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SELECT @PLANT_CODE = plant
FROM OC_CONFIG_SYSTEM
WHERE system_name = @PC_NAME;

SET @Exists = CASE WHEN @PLANT_CODE IS NULL THEN 0 ELSE 1 END;
END
GO

[Eric1977]

I have some new information. I was finally able to get data returned! On the DB Tools Execute Query, I had to add another question mark to the call, so now it's:

{call uspGetPlantProductList_Select (?,?,?)}

Which retrieves the vales that I want other than the Plant Code which only comes back as one character instead of four like I am expecting. I am still investigating that part.

[Yamaeda]

You have 3 @-declarations, so you needing 3 @s in the call is logical.

Without seeing anything about the table it's hard to guess why Plant is 1 character. Is it an ID-number (int)  converted to a unicode char?

[Eric1977 · Accepted solution]

I figured it out. Apparently, when you assign parameters to the DB Tools Create Parameterized Query VI, you are supposed to set how many characters you wish to return in the 'value' portion of the Array Cluster. The documentation does not specify that - at least for strings.

I found this out by looking at this Knowledge Base article: Set and Get Input and Output Parameters from a SQL Stored Procedure - NI

See #4, the second bullet point.

Reserve memory space for the output parameter before the query is executed.

• This can be accomplished by entering a default value in the Initial Value field of the cluster.  This initial default value must be at least as long as the output parameter value that you are expecting.  

Once I did that, wa-la! The appropriate value appeared.