sp_executesql 为什么得不到正确的结果

[blue_morning]

//这是代码
在执行这段代码后为什么'索要回扣'这个字段的值还是NULL
DECLARE @QuesCount Numeric(18,0)
DECLARE @FieldName Varchar(50)
DECLARE @ProjTypeID UNIQUEIDENTIFIER
DECLARE @SqlStr NVARCHAR(500)

SET @ProjTypeID = '{9BD26AED-F54B-4942-B58D-7DCD5227CD01}'
set @FieldName = '索要回扣'
set @QuesCount = 100

SET @SqlStr = 'UPDATE TEMP_TEST SET @P1 = @P2 WHERE ID = @P3'
EXECUTE sp_executesql @SqlStr,N'@P1 VARCHAR(50), @P2 NUMERIC(18, 0), @P3 UNIQUEIDENTIFIER',
@P1 = @FieldName, @P2 = @QuesCount, @P3 = @ProjTypeID

select * from temp_test

//---------------------------这是表和数据---------------------------
CREATE TABLE [dbo].[TEMP_TEST] (
[ID] [uniqueidentifier] NULL ,
[索要回扣] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into TEMP_TEST (ID) values('{9BD26AED-F54B-4942-B58D-7DCD5227CD01}')

[zjcxc]

UPDATE TEMP_TEST SET @P1 = @P2 WHERE ID = @P3

这个实际是为变量赋值,根本就没有更新表中的字段

[zjcxc]

字段名不能用变量表示

[zjcxc]

--这样改

CREATE TABLE [dbo].[TEMP_TEST] (
[ID] [uniqueidentifier] NULL ,
[索要回扣] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into TEMP_TEST (ID) values('{9BD26AED-F54B-4942-B58D-7DCD5227CD01}')
go

DECLARE @QuesCount Numeric(18,0)
DECLARE @FieldName Varchar(50)
DECLARE @ProjTypeID UNIQUEIDENTIFIER
DECLARE @SqlStr NVARCHAR(500)

SET @ProjTypeID = '{9BD26AED-F54B-4942-B58D-7DCD5227CD01}'
set @FieldName = '索要回扣'
set @QuesCount = 100

SET @SqlStr = 'UPDATE TEMP_TEST SET ['+@FieldName+'] = @P2 WHERE ID = @P3'
EXECUTE sp_executesql @SqlStr,N'@P1 VARCHAR(50), @P2 NUMERIC(18, 0), @P3 UNIQUEIDENTIFIER',
@P1 = @FieldName, @P2 = @QuesCount, @P3 = @ProjTypeID

select * from temp_test

go

drop table temp_test

/*--测试结果

ID 索要回扣
------------------------------------ --------------------
9BD26AED-F54B-4942-B58D-7DCD5227CD01 100

（所影响的行数为 1 行）
--*/

[vinsonshen]

改成这样就行了：

DECLARE @QuesCount Numeric(18,0)
DECLARE @FieldName Varchar(50)
DECLARE @ProjTypeID UNIQUEIDENTIFIER


SET @ProjTypeID = '{9BD26AED-F54B-4942-B58D-7DCD5227CD01}'
set @FieldName = '索要回扣'
set @QuesCount = 100

exec ('UPDATE TEMP_TEST SET ['+@FieldName+'] = '''+@QuesCount+''' WHERE ID = '''+@ProjTypeID+'''')

[jim138]

ding

[jim138]

mark

[blue_morning]

zjcxc(邹建)：麻烦你到这个帖子来登记一下
http://community.csdn.net/Expert/topic/3687/3687270.xml?temp=.4878961
http://community.csdn.net/Expert/topic/3688/3688907.xml?temp=.6090509

所谓会者不难，原来如此。