Update Statement Not Executing

I am trying to write an UPDATE query that can check for a null value in one of the fields. I have three conditions, two are mandatory, of three fields. itemCode and itemCheckDigit

Solution 1:

Your problem is that you are wrapping tick marks around your parameters in your statement so when it's evaluated it looking for stuff from the item table where itemCode is 'toy' (note the single quotes)

The string concatenation you are doing is how one would, poorly, add parameters to their dynamic queries. Instead, take the tick marks out like so

UPDATE
    item
SET
    itemImageFileName =@fileNameWHERE
    (itemCode =@itemCode5 ) 
    AND (itemCheckDigit =@itemCkDigit) 
    AND (itemSuffix ISNULL); 

To handle optional search parameters, this article by Bill Graziano is excellent: Using Dynamic SQL in Stored Procedures. I find that is strikes a good balance between avoiding the query recompilations of setting the recompile option on and avoiding table scans.

Please to enjoy this code. It create a temporary table to simulate your actual item table and loads it up with 8 rows of data. I declare some parameters which you most likely wont' need to do as the ado.net library will do some of that magic for you.

Based on the values supplied for the first 3 parameters, you will get an equivalent match to a row in the table and will update the filename value. In my example, you will see the all NULL row will have the filename changed from f07.bar to f07.bar.updated.

The print statement is not required but I put it in there so that you can see the query that is built as an aid in understanding the pattern.

IF NOTEXISTS (SELECT*FROM tempdb.sys.tables T WHERE T.name like'%#item%')
BEGINCREATETABLE 
        #item
    (
        itemid intidentity(1,1) NOTNULLPRIMARY KEY
    ,   itemCode varchar(10) NULL
    ,   itemCheckDigit varchar(10) NULL
    ,   itemSuffx varchar(10) NULL
    ,   itemImageFileName varchar(50) 
    )
    INSERTINTO
        #item
    -- 2008+--table value constructor (VALUES allows for anonymous table declaration) {2008}--http://technet.microsoft.com/en-us/library/dd776382.aspxVALUES 
        ('abc', 'X', 'cba', 'f00.bar')
    ,   ('ac', NULL, 'ca', 'f01.bar')
    ,   ('ab', 'x', NULL, 'f02.bar')
    ,   ('a', NULL, NULL, 'f03.bar')

    ,   (NULL, 'X', 'cba', 'f04.bar')
    ,   (NULL, NULL, 'ca', 'f05.bar')
    ,   (NULL, 'x', NULL, 'f06.bar')
    ,   (NULL, NULL, NULL, 'f07.bar')
ENDSELECT*FROM #item I;

-- These correspond to your parametersDECLARE@itemCode5varchar(10)
,   @itemCkDigitvarchar(10)
,   @itemSuffxvarchar(10)
,   @fileNamevarchar(50)

-- Using the above table, populate these as-- you see fit to verify it's behaving as expected-- This example is for all NULLs SELECT@itemCode5=NULL
,   @itemCkDigit=NULL
,   @itemSuffx=NULL
,   @fileName='f07.bar.updated'DECLARE@query nvarchar(max)

SET@query= N'
UPDATE
    I
SET
    itemImageFileName = @fileName
FROM
    #item I
WHERE
    1=1
' ;


IF @itemCode5ISNOTNULLBEGINSET@query+='    AND I.itemCode = @itemCode5 '+char(13)
ENDELSEBEGIN-- These else clauses may not be neccessary depending on -- what your data looks like and your intentionsSET@query+='    AND I.itemCode IS NULL '+char(13)
END

IF @itemCkDigitISNOTNULLBEGINSET@query+='    AND I.itemCheckDigit = @itemCkDigit '+char(13)
ENDELSEBEGINSET@query+='    AND I.itemCheckDigit IS NULL '+char(13)
END

IF @itemSuffxISNOTNULLBEGINSET@query+='    AND I.itemSuffx = @itemSuffx '+char(13)
ENDELSEBEGINSET@query+='    AND I.itemSuffx IS NULL '+char(13)
END

PRINT @queryEXECUTE sp_executeSQL @query   
,   N'@itemCode5 varchar(10), @itemCkDigit varchar(10), @itemSuffx varchar(10), @fileName varchar(50)'
,   @itemCode5=@itemCode5
,   @itemCkDigit=@itemCkDigit
,   @itemSuffx=@itemSuffx
,   @fileName=@fileName;

-- observe that all null row is now displaying-- f07.bar.updated instead of f07.barSELECT*FROM #item I;

Before

itemid  itemCode   itemCheckDigit   itemSuffx    itemImageFileName
1       abc        X                cba          f00.bar
2       ac         NULL             ca           f01.bar
3       ab         x                NULL         f02.bar
4       a          NULLNULL         f03.bar
5NULL       X                cba          f04.bar
6NULLNULL             ca           f05.bar
7NULL       x                NULL         f06.bar
8NULLNULLNULL         f07.bar

after

itemid  itemCode   itemCheckDigit   itemSuffx    itemImageFileName
1       abc        X                cba          f00.bar
2       ac         NULL             ca           f01.bar
3       ab         x                NULL         f02.bar
4       a          NULLNULL         f03.bar
5NULL       X                cba          f04.bar
6NULLNULL             ca           f05.bar
7NULL       x                NULL         f06.bar
8NULLNULLNULL         f07.bar.updated