Cannot pass nulls over to sql server 2005 using .NET and a stored procedure

Posted on 2007-07-31
Last Modified: 2013-11-26
Using VB.NET 2005, talking to SQL Server 2005 using a stored procedure, I am having a heck of a time trying to give an official NULL to the database whenever a user has not entered a particular value in a field. I was sending empty strings, but the folks on the SQL end need official NULLs for any empty field.

In .NET, I am doing an executeQuery with my stored procedure name and the parameters from the textboxes on the GUI (am actually doing this through the MS SQLHelper class). Note that everything compiles fine. The stored procedure itself looks like the following (at the beginning, youll see various different attempts at getting this to work):

ALTER PROCEDURE [dbo].[save_similiar_data]
@tableName VARCHAR(10) = '',
@comment VARCHAR(255)= '',
@disposition VARCHAR(20)= '',
@uac1 VARCHAR(46)= '',
@uac2 VARCHAR(46)= '',
@feedback1 VARCHAR(35)= '',
@feedback2 VARCHAR(35)= '',
@feedback3 VARCHAR(35)= '',
@ncr VARCHAR(35)= '',
@coder VARCHAR(20)= '',
@fClass VARCHAR(20)= '',
@status VARCHAR(37)= '',
@rProvider VARCHAR(40)= '',
@bProvider VARCHAR(40)= '',
@rfProvider VARCHAR(40)= '',


DECLARE @sqlStatement VARCHAR(500)

SET @tName = (SELECT dbo.getTDTableName(@tableName))

--Note that char(44) is the ASCII comma and char(39) is the ASCII single quote.
SET @sqlStatement = 'UPDATE ' + @tName + ' SET '
if Not @comment like '""'
SET @sqlStatement = @sqlStatement + '[Comment:] = ' + char(39) + @comment +  char(39)

if Not @disposition like '""'
SET @sqlStatement = @sqlStatement + char(44) + ' [Disposition:] = ' + char(39) + @disposition +  char(39)

--SET @sqlStatement = @sqlStatement + char(44) + ' [Unable_to_Code_Reason:] = ' + (case @uac1
--when '' then NULL
--else char(39) + @uac1 +  char(39)

--if @feedback1 <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Feedback_Reason:] = ' + char(39) + @feedback1 +  char(39)

--if @fClass <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Financial_Class:] = ' + char(39) + @fClass +  char(39)
--if @status <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Status:] = ' + char(39) + @status +  char(39)

SET @sqlStatement = @sqlStatement + ' WHERE TocId = ' + @lfDocID

--Need to do this way due to the dynamic passing of the tablename

            RAISERROR('Record not found.', 16, 1)
            RETURN 1

I have tried various things, but no NULL shows up in the database cell for the empty fields. Ive tried default values of , default values of , and default values of NULL. Ive tried literally passing NULL. It seems, the only time that NULL shows up in the database cell is when I do not set the empty fields AT ALL in my my update statement. However, in order to do that, I need to put each field in an if-statement to see if I should add it to the sqlstatement. That has also been problematic as it falls through the if when I check it for , NULL, and even char(39)+char(39) for , among others.

Bottom line is, in the Update statement, I need to either not set any empty string field OR give the database official NULLs for these empty fields. It sounds like such a simple damn thing. Please respond if you are a SQL Server expert and are sure your idea will work as Ive already been working on this for several days, and, I believe, Ive tried everything that the average user would have tried. Thank you!!!
Question by:JavaAndStuff
    LVL 42

    Accepted Solution

    There are several ways to accomplish that.  From where you are now, probably the easiest is to use the NULLIF function in your procedure.  

    The eventual SQL string should look like this:

    UPDATE YourTable
       SET YourColumn=NULLIF('yourtextparameter','')
      ,SET ...
    WHERE ...

    However, the challenge is distinguishing setting a column to NULL from not updating it.  As it stands, you need to update every column every time. Maybe that's OK, but if not, you can to backup a step and defaults to NULL, i.e.
    ALTER PROCEDURE [dbo].[save_similiar_data]
    @tableName VARCHAR(10) = NULL,
    @comment VARCHAR(255)= NULL,
    @disposition VARCHAR(20)= NULL,

    Then a NULL in the parameter means do not update it and an empty string means update it to NULL.  Something like:

    IF @comment is not null
       SET @sqlStatement = @sqlStatement + NULLIF('[Comment:] = ' + char(39) + @comment +  char(39),'')


    LVL 12

    Assisted Solution

    I'm not clear on exactly what you need. However, I think you what to update a NULL in a field if the parameter is an empty string or an actual NULL.  If so, ....

    SET @sqlStatement = @sqlStatement + char(44) + ' [Unable_to_Code_Reason:] = ' +
                  (case when IsNull(@uac1,'') = '' then 'NULL'
                    else char(39) + @uac1 +  char(39) end)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now