更新语句未执行 [英] update statement not executing
问题描述
我正在尝试编写一个 UPDATE 查询，该查询可以检查其中一个字段中的空值.我有三个条件，两个是强制性的，三个字段.itemCode 和 itemCheckDigit 将始终存在，itemSuffix 可能为 Null.
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 possibily Null.
itemCode 和 itemCheckDigit 一起等于其他记录的可能有多个记录，itemSuffix 是非静态标识符，虽然 itemSuffix 为 Null 只会存在一个实例，并且永远不会重复.
There may be multiple records where itemCode and itemCheckDigit together are equal to other records, itemSuffix is the unquie identifier, only one instance will exist though where 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 is not working.
推荐答案
你的问题是你在你的语句中的参数周围加上刻度线，所以当它被评估时，它会从 itemCode 是 'toy' 的 item 表中寻找东西(注意单引号)
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 = @fileName
WHERE
(itemCode = @itemCode5 )
AND (itemCheckDigit = @itemCkDigit)
AND (itemSuffix IS NULL);
为了处理可选的搜索参数，Bill Graziano 的这篇文章非常出色:在存储过程中使用动态 SQL.我发现这在避免将重新编译选项设置为 on 的查询重新编译和避免表扫描之间取得了很好的平衡.
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.
请享受此代码.它创建一个临时表来模拟您的实际项目表，并用 8 行数据加载它.我声明了一些您很可能不需要做的参数，因为 ado.net 库会为您做一些魔术.
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.
根据为前 3 个参数提供的值，您将获得与表中一行的等效匹配项，并将更新文件名值.在我的示例中，您将看到所有 NULL 行的文件名将从 f07.bar 更改为 f07.bar.updated.
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.
print 语句不是必需的，但我把它放在那里，这样你就可以看到作为帮助理解模式而构建的查询.
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 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'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 @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;
之前
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
之后
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
这篇关于更新语句未执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
