Update instruction is not executed

Question

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 will always exist, itemSuffix is ​​possibly Null.

There may be several entries where itemCode and itemCheckDigit along with other entries, itemSuffix is ​​an unquie identifier, only one instance will exist if itemSuffix is ​​Null and it will never be duplicated.

UPDATE item SET itemImageFileName = ''' + @fileName + ''' WHERE (itemCode = ''' + @itemCode5 + ''') AND (itemCheckDigit = ''' + @itemCkDigit + ''') AND (itemSuffix IS NULL); 

This is what I think I would like to do, but it does not work.

Answer 1

Your problem is that you swap marks around your parameters in your application, so when he evaluates his search for things from the element table where itemCode is a "toy" (note the single quotes)

The string concatenation you are doing is how it might be bad to add parameters to your dynamic queries. Instead, check this:

 UPDATE item SET itemImageFileName = @fileName WHERE (itemCode = @itemCode5 ) AND (itemCheckDigit = @itemCkDigit) AND (itemSuffix IS NULL); 

To handle optional search parameters, this article by Bill Graziano is excellent: Using dynamic SQL in stored procedures . I believe this is a good balance between avoiding recompiling the query when setting the recompilation option and avoiding table scans.

Please enjoy this code. It creates a temporary table to simulate your actual item table and loads it using 8 rows of data. I am announcing some parameters that you most likely will not need, as the ado.net library will do some magic for you.

Based on the values ​​provided for the first 3 parameters, you will get an equivalent match for the row in the table and update the file name value. In my example, you will see that the entire NULL string will have the file name changed from f07.bar to f07.bar.updated.

A print statement is not required, but I put it there so you can see the query that was created to help understand the pattern.

 IF NOT EXISTS (SELECT * FROM tempdb.sys.tables T WHERE T.name like '%#item%') BEGIN CREATE TABLE #item ( itemid int identity(1,1) NOT NULL PRIMARY KEY , itemCode varchar(10) NULL , itemCheckDigit varchar(10) NULL , itemSuffx varchar(10) NULL , itemImageFileName varchar(50) ) INSERT INTO #item -- 2008+ --table value constructor (VALUES allows for anonymous table declaration) {2008} --http://technet.microsoft.com/en-us/library/dd776382.aspx VALUES ('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') END SELECT * FROM #item I; -- These correspond to your parameters DECLARE @itemCode5 varchar(10) , @itemCkDigit varchar(10) , @itemSuffx varchar(10) , @fileName varchar(50) -- Using the above table, populate these as -- you see fit to verify it 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 @itemCode5 IS NOT NULL BEGIN SET @query += ' AND I.itemCode = @itemCode5 ' + char(13) END ELSE BEGIN -- These else clauses may not be neccessary depending on -- what your data looks like and your intentions SET @query += ' AND I.itemCode IS NULL ' + char(13) END IF @itemCkDigit IS NOT NULL BEGIN SET @query += ' AND I.itemCheckDigit = @itemCkDigit ' + char(13) END ELSE BEGIN SET @query += ' AND I.itemCheckDigit IS NULL ' + char(13) END IF @itemSuffx IS NOT NULL BEGIN SET @query += ' AND I.itemSuffx = @itemSuffx ' + char(13) END ELSE BEGIN SET @query += ' AND I.itemSuffx IS NULL ' + char(13) END PRINT @query EXECUTE 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.bar SELECT * FROM #item I; 

Front

 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 NULL NULL f03.bar 5 NULL X cba f04.bar 6 NULL NULL ca f05.bar 7 NULL x NULL f06.bar 8 NULL NULL NULL 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 NULL NULL f03.bar 5 NULL X cba f04.bar 6 NULL NULL ca f05.bar 7 NULL x NULL f06.bar 8 NULL NULL NULL f07.bar.updated