各位朋友,,,现在本人遇到一个麻烦的问题,,,就是按照多个条件 更新动态列表名 payitem ,动态列名 c1 ,c2, c3, c4 等 ,条件是固定的 id ,name, place 现在我是想这样,,, update payitem set 动态列名=动态值
where id =传入的值 and name =传入的值 and place =传入的值
请问怎么样写呢!!!
where id =传入的值 and name =传入的值 and place =传入的值
请问怎么样写呢!!!
exec('update payitem set '+@动态列名+'='+@动态值+ '
where id ='+@传入的值+' and name ='+@传入的值+' and place ='+@传入的值 )???
alter proc p_pay_SalaryByLedger_project
@companyid varchar(50),
@periodid varchar(50),
@PayitemField varchar(50), --栏目字段
@cccode varchar(50),
@scccode varchar(50),
@hrcode varchar(50),
@paytypecode varchar(50),
@positionid varchar(50),
@moneytypecode varchar(50),
@digit varchar(50)
as
begin
-----------------首先检查Pay_SalaryByLedger表中的字段是否匹配 if not exists (select 1 from syscolumns where id =object_id('Pay_SalaryByLedger') and name=@PayitemField)
begin
raiserror('工资表没有这个字段,栏目录入错误!请检查!!!',18,1)
return end exec ('update Pay_SalaryByLedger set '+@PayitemField+'='+@digit+'
where Companyid ='+@companyid+ )
end
exec ('update Pay_SalaryByLedger set '+@PayitemField+'='+@digit+'
where Companyid ='+@companyid )最后多了一个加号
if object_id('[payitem]') is not null drop table [payitem]
go
create table [payitem]([id] int,[name] varchar(1),[place] varchar(1))
insert [payitem]
select 1,'a','b'select * from [payitem]
declare @id varchar(50)
set @id=1
declare @name varchar(50)
set @name='a'
declare @place varchar(50)
set @place='b'declare @colname varchar(50)
set @colname='name'declare @colvalue varchar(50)
set @colvalue='c'exec('update [payitem] set '+@colname+'='''+@colvalue+''' where id ='+@id+' and name ='''+@name+''' and place ='''+@place+''' ')select * from [payitem]/*
id name place
----------- ---- -----
1 a b(1 行受影响)(1 行受影响)id name place
----------- ---- -----
1 c b(1 行受影响)
*/
再用 exec(sql)来执行
where Companyid ='+@companyid+ )看看
where Companyid ='''+@companyid+'''
@companyid varchar(50),
@periodid varchar(50),
@PayitemField varchar(50), --栏目字段
@cccode varchar(50),
@scccode varchar(50),
@hrcode varchar(50),
@paytypecode varchar(50),
@positionid varchar(50),
@moneytypecode varchar(50),
@digit varchar(50)
as
begin
-----------------首先检查Pay_SalaryByLedger表中的字段是否匹配 if not exists (select 1 from syscolumns where id =object_id('Pay_SalaryByLedger') and name=@PayitemField)
begin
raiserror('工资表没有这个字段,栏目录入错误!请检查!!!',18,1)
return end
--DECLARE @companyid varchar(50),@PayitemField varchar(50), @digit varchar(50)
--SET @companyid='A'
--SET @PayitemField='B'
--SET @digit='C' PRINT ('update Pay_SalaryByLedger set '+@PayitemField+'='''+@digit+'''
where Companyid ='''+@companyid+'''')
/*update Pay_SalaryByLedger set B='C'
where Companyid ='A'*/
end 字符型是要用引号引起来的,在动态中''''两点表一点
set @tb = 'payitem'
declare @col as varchar(10)
set @col = 'col'....然后拼接动态SQL
例如:declare @sql as varchar(100)
set @sql = 'update ' + @tb + ' set ' + @col + ' ...............'exec(@sql)以下是动态SQL的基本用法.--动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num