SQL Server stored procedure Nullable parameter

Question

Problem  When values are provided to the following script then executed using a setup in C  like below  or in SQL Server environment  the values do not update in the database   Stored procedure   -- Updates the Value of any type of PropertyValue --  Type meaining simple Value  UnitValue  or DropDown  CREATE PROCEDURE  dbo   usp UpdatePropertyValue       PropertyValueID int       Value varchar max    NULL       UnitValue float   NULL       UnitOfMeasureID int   NULL       DropDownOptionID int   NULL AS BEGIN        -- If the Property has a  Value  Update it      IF  Value IS NOT NULL     BEGIN         UPDATE  dbo   PropertyValue          SET             Value    Value         WHERE              dbo   PropertyValue   ID     PropertyValueID     END     -- Else check if it has a  UnitValue  amp  UnitOfMeasureID     ELSE IF  UnitValue IS NOT NULL AND  UnitOfMeasureID IS NOT NULL     BEGIN         UPDATE  dbo   UnitValue          SET             UnitValue    UnitValue              UnitOfMeasureID    UnitOfMeasureID         WHERE              dbo   UnitValue   PropertyValueID     PropertyValueID               END     -- Else check if it has just a  UnitValue     ELSE IF  UnitValue IS NOT NULL AND  UnitOfMeasureID IS NULL     BEGIN         UPDATE  dbo   UnitValue          SET             UnitValue    UnitValue         WHERE              dbo   UnitValue   PropertyValueID     PropertyValueID       END     -- Else check if it has a  DropDownSelection to update      ELSE IF  DropDownOptionID IS NULL     BEGIN         UPDATE  dbo   DropDownSelection          SET             SelectedOptionID    DropDownOptionID         WHERE              dbo   DropDownSelection   PropertyValueID     PropertyValueID     END END   When I do an execution of this script  like below  it does not update any values   Example execution   String QueryString    EXEC  dbo   usp UpdatePropertyValue   PropertyValueID   Value   UnitValue   UnitOfMeasureID   DropDownOptionID   SqlCommand Cmd   new SqlCommand QueryString  this  DbConn    Cmd Parameters Add new SqlParameter   PropertyValueID   System Data SqlDbType Int    Cmd Parameters Add new SqlParameter   Value   System Data SqlDbType Int    Cmd Parameters Add new SqlParameter   UnitValue   System Data SqlDbType Int    Cmd Parameters Add new SqlParameter   UnitOfMeasureID   System Data SqlDbType Int    Cmd Parameters Add new SqlParameter   DropDownOptionID   System Data SqlDbType Int     Cmd Parameters   PropertyValueID   Value   Property Value ID     1 Cmd Parameters   Value   IsNullable   true  Cmd Parameters   Value   Value   DBNull Value  Cmd Parameters   UnitValue   IsNullable   true  Cmd Parameters   UnitValue   Value   DBNull Value  Cmd Parameters   UnitOfMeasureID   IsNullable   true  Cmd Parameters   UnitOfMeasureID   Value   DBNull Value  Cmd Parameters   DropDownOptionID   IsNullable   true  Cmd Parameters   DropDownOptionID   Value   2     Current Value in DB  3   Details   After running an execute  via C  code or SQL Server environment  it does not update dbo DropDownSelection SelectedOptionID  I m guessing that it might be because dbo DropDownSelection SelectedOptionID is non-nullable and the parameter I m using to set it is nullable  despite that when setting it shouldn t ever be null   Upon execution the return value is 0  If I run one of the Updates outside of the procedure they work perfectly  hence my suspicion that it has to do with null-able types   Question s    Could this be because the parameters to the stored procedure are nullable and the fields I m setting aren t   If not  what could it be

User · Answer

You can should set your parameter to value to DBNull Value   if  variable               cmd Parameters Add   Param   SqlDbType VarChar  500  Value   DBNull Value    else        cmd Parameters Add   Param   SqlDbType VarChar  500  Value   variable      Or you can leave your server side set to null and not pass the param at all

User · Answer

It looks like you re passing in Null for every argument except for PropertyValueID and DropDownOptionID  right   I don t think any of your IF statements will fire if only these two values are not-null   In short  I think you have a logic error   Other than that  I would suggest two things     First  instead of testing for NULL  use this kind syntax on your if statements  it s safer          ELSE IF ISNULL  UnitValue  0     0 AND ISNULL  UnitOfMeasureID  0    0   Second  add a meaningful PRINT statement before each UPDATE   That way  when you run the sproc in MSSQL  you can look at the messages and see how far it s actually getting

[sql-server] SQL Server stored procedure Nullable parameter

Examples related to sql-server

Examples related to stored-procedures

Examples related to sql-server-2008-r2