LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How to write NULL values using parameterised query or stored procedure

This was a very old thread, but I'll add this in case anyone looks at it. I'm using LV2012.

 

If you input a Database Variant for a parameter value it sometimes gives you a NULL in a database field. This works for a number, but does not work for strings and binarys. I tried using a long integer. I did not try the other numerics because if it doesn't work for all datatypes I can't use and don't want to take any more time. Putting a DB NULL in for the Date/Time type returns an error.

0 Kudos
Message 11 of 12
(537 Views)

I don't know if this is recently introduced capability or not, but with LV 2018 simply typing in the string NULL without any quotes seems to work if you use instead the simple 'DB Tools Execute Query.vi'   I tested this with the SQL int, varchar, and bit data types.

 

Here's the example...

SQL Query string:

EXEC usp_UPDATE_MY_TABLE NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'3000',NULL,NULL,NULL,NULL,NULL,'7';

 

  -- This stored procedure takes in 15 parameters in total.  Note: The very last parameter is required to execute the stored procedure.  I wanted functionality that would allow for input of either all, some, or one individual optional parameter argument, where a NULL value would indicate to keep the existing column value from a specified ID record (7) in the table.

 

User Stored Procedure:

USE [myDataBase]

 

GO

/****** Object: StoredProcedure [dbo].[usp_UPDATE_MY_TABLE] Script Date: 3/25/2019 9:03:06 PM ******/

SET ANSI_NULLS ON

 

GO

SET QUOTED_IDENTIFIER ON

 

GO

-- =============================================

-- Description: Updates a button definition in myTable given the required ID input parameter.

-- Instructions: Passing NULL (without single quotes) from LabVIEW will push a null argument over to SQL.

-- Passing a NULL valued input parameter will force this procedure to keep the existing column value stored for this record in the table.

--

-- =============================================

ALTER PROCEDURE [dbo].[usp_UPDATE_MY_TABLE] @OPT1 varchar(50), @OPT2 varchar(255),@OPT3 varchar(255),

@OPT4 varchar(255), @OPT5 varchar(255), @OPT6 varchar(255), @OPT7 varchar(255),

@OPT8 varchar(255), @OPT9 varchar(255), @OPT10 varchar(255), @OPT11 varchar(255),

@OPT12 varchar(255), @OPT13 varchar(255), @OPT14 varchar(255),

@REQ1 int

-- Add the parameters for the stored procedure here

 

AS

BEGIN

IF @OPT1 IS NULL SET @OPT1 = (SELECT ONE FROM myTable WHERE ID = @REQ1);

IF @OPT2 IS NULL SET @OPT2 = (SELECT TWO FROM myTable WHERE ID = @REQ1);

IF @OPT3 IS NULL SET @OPT3  = (SELECT THREE FROM myTable WHERE ID = @REQ1);

IF @OPT4 IS NULL SET @OPT4  = (SELECT FOUR FROM myTable WHERE ID = @REQ1);

IF @OPT5 IS NULL SET @OPT5  = (SELECT FIVE FROM myTable WHERE ID = @REQ1);

IF @OPT6 IS NULL SET @OPT6  = (SELECT SIX FROM myTable WHERE ID = @REQ1);

IF @OPT7 IS NULL SET @OPT7  = (SELECT SEVEN FROM myTable WHERE ID = @REQ1);

IF @OPT8 IS NULL SET @OPT8  = (SELECT EIGHT FROM myTable WHERE ID = @REQ1);

IF @OPT9 IS NULL SET @OPT9  = (SELECT NINE FROM myTable WHERE ID = @REQ1);

IF @OPT10 IS NULL SET @OPT10 = (SELECT TEN FROM myTable WHERE ID = @REQ1);

IF @OPT11 IS NULL SET @OPT11 = (SELECT ELEVEN FROM myTable WHERE ID = @REQ1);

IF @OPT12 IS NULL SET @OPR12 = (SELECT TWELVE FROM myTable WHERE ID = @REQ1);

IF @OPT13 IS NULL SET @OPT13 = (SELECT THIRTEEN FROM myTable WHERE ID = @REQ1);

IF @OPT14 IS NULL SET @OPT14 = (SELECT FOURTEEN FROM myTable WHERE ID = @REQ1);

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

UPDATE myTable SET ONE = @OPT1, TWO = @OPT2, THREE = @OPT3, FOUR = @OPT4,FIVE = @OPT5, SIX = @OPT6, SEVEN = @OPT7, EIGHT = @OPT8, NINE = @OPT9, TEN = @OPT10, ELEVEN = @OPT11, TWELVE = @OPT12, THIRTEEN = @OPT13, FOURTEEN = @OPT14

WHERE ID = @REQ1;

0 Kudos
Message 12 of 12
(343 Views)