Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: if (@param IS NULL) Not Working

    ALTER PROCEDURE dbo.CompanyProducts
    (
    @CompanyID int,
    @ProductName nvarchar(255) = NULL
    )
    AS
    BEGIN
    IF (@ProductName IS NOT NULL)
    BEGIN
    SELECT ...
    END
    IF (@ProductName IS NULL)
    BEGIN
    SELECT...
    END
    END

    Is there a reason why when @ProductName is null the SELECT statement under the "IS NULL" condition does not execute? When I set @ProductName = 'Hello' in the second statement and submit the value as "Hello," the SELECT executes. It seems like I've run into this before, but I can't remember how to resolve it... Any suggestions?

    Thanks
    Last edited by bubbisthedog; 06-28-10 at 17:02.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When I set @ProductName = 'Hello' in the second statement and submit the value as "Hello,
    Where are you submitting this from, exactly?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2010
    Posts
    3
    Quote Originally Posted by Teddy View Post
    Where are you submitting this from, exactly?
    An .aspx Web page. It's a text input field. When the 'Submit' button is clicked the value is passed into the @ProductName parameter in a stored procedure, which then executes a particular SELECT statement depending on whether or not the value is null.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Thought so...

    This is a .NET issue, not a MSSQL problem.

    What you're most likely seeing is ADO.NET sending an empty string when you intended for it to send null. You can confirm this by hooking up a profiler trace and examining the values that are ACTUALLY passed to sql server.

    I don't remember what object owns it right off the top of my head, but either the connection, command or adapter has a bool that you can toggle for "treat null values as empty strings" or something to that affect. I think that will take care of you. Alternately, you can do your own logic and simply omit the SqlParameter entirely if you intended for it to be null. This will force MSSQL take care of defaulting the optional parameter since it was not supplied.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2010
    Posts
    3

    Thumbs up

    Quote Originally Posted by Teddy View Post
    Thought so...

    This is a .NET issue, not a MSSQL problem.

    What you're most likely seeing is ADO.NET sending an empty string when you intended for it to send null. You can confirm this by hooking up a profiler trace and examining the values that are ACTUALLY passed to sql server.

    I don't remember what object owns it right off the top of my head, but either the connection, command or adapter has a bool that you can toggle for "treat null values as empty strings" or something to that affect. I think that will take care of you. Alternately, you can do your own logic and simply omit the SqlParameter entirely if you intended for it to be null. This will force MSSQL take care of defaulting the optional parameter since it was not supplied.
    ConvertEmptyStringtoNull is a parameter property that I changed in the "Configure Data Source" GridView wizard from "True" to "False" and now it works.

    You directed me down the correct path; I really appreciate it, Teddy.

    Take care,

    bubbis
    Last edited by bubbisthedog; 06-28-10 at 17:46. Reason: RESOLVED

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •