问题:现在通过declare @tmpColunName varchar(100)
declare @tmpValue varchar(100)declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
如何把取的 @tmpColunName 每个表列名当做表Users去查询它具体值? 如:select @tmpValue = @tmpColunName(列名) from users where userid = 3;(主要是这句SQL,我写的不对,不知道取的列值如何转化为真正的列名?)
if @tmpValue is not null
bein
end
else
begin
end
END
declare @tmpValue varchar(100)declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
如何把取的 @tmpColunName 每个表列名当做表Users去查询它具体值? 如:select @tmpValue = @tmpColunName(列名) from users where userid = 3;(主要是这句SQL,我写的不对,不知道取的列值如何转化为真正的列名?)
if @tmpValue is not null
bein
end
else
begin
end
END
declare @tmpValue varchar(100)declare @sql varchar(8000)
set @sql='select 'declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=@sql+@tmpColunName+','
fetch NEXT from column_Cursor into @tmpColunName;
END
set @sql=left(@sql,len(@sql)-1)+' from users where userid = 3'
exec(@sql)
if @@rowcount>0
begin
end
else
begin
end
declare @tmpValue varchar(100)declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
declare @n nvarchar(200)
set @n='select @tmp = '+@tmpColunName+' from users where userid = 3';
EXEC @n,N'@tmp varchar(100) output',@tmpValue output
if @tmpValue is not null
begin
--
end
else
begin
--
end
END
close column_Cursor
deallocate column_Cursor
declare @tmpValue varchar(100)declare @sql nvarchar(4000)declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
if @@FETCH_STATUS = 0
set @sql='select @tmpValue='+@tmpColunName+' from users where userid = 3'
exec sp_executesql @sql,N'@a varhcar(100) output' ,@tmpValue output
if @tmpValue is not null
begin
end
else
begin
end
提示:“参数或变量 '@tmpValue' 的数据类型无效”
select * from users
/*
userid use_name
----------- --------------------
1 ashen
2 age
3 ado
4 AA
5 BB
6 CC(所影响的行数为 6 行)
*/
declare @tmpColunName varchar(100)
declare @tmpValue varchar(100)
declare @sql nvarchar(4000)
declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
while @@FETCH_STATUS = 0
begin
set @sql='select @a='+@tmpColunName+' from users where userid = 3'
exec sp_executesql @sql,N'@a varchar(100) output' ,@tmpValue output
if @tmpValue is not null
begin
print @tmpValue
end
else
begin
print 'null'
end
fetch NEXT from column_Cursor into @tmpColunName;
end
close column_Cursor
deallocate column_Cursor/*
3
ado
*/
1。 就是A机器运行的触发器“增、删、改”通过SOCKET发动B机器上执行表的触发器功能;
2。 但每个表的字段不同,我用程序动态生成了每个表的字段以及“增、删、改”SQL语句
3。 但是发现在“修改”语句的时候,如果某一个字段为空,那么就不能生成SQL语句,这样也就没有办法socket发到B机器了,所以我要对每个表的字段是否有值进行校验,只需有数据的,为空的就不拼写SQL语句出来;
第 1 个列、参数或变量: 找不到数据类型 varhcar。
参数或变量 '@tmp' 的数据类型无效。
第 1 个列、参数或变量: 找不到数据类型 varhcar。
参数或变量 '@tmp' 的数据类型无效。
把你执行的语句贴出来看看
ALTER trigger [update_users_trigger] on [dbo].[users] for update
as
declare @CurrDT varchar(50)
declare @SQLText nvarchar(2000)
declare @DELSQL nvarchar(2000)
declare @KeyValue int
declare @tmpColunName varchar(1000)
declare @tmpValue varchar(500)
declare @sql nvarchar(1000)
declare @tmp varchar(100)
--declare @count intdeclare @userid varchar(500)
begin set @CurrDT = Convert(varchar(20),getdate(),120);
select @KeyValue = currKeyValue from RSYKeyValue where id = 1;
update RSYKeyValue set currKeyValue = currKeyValue + 1 where id = 1;
SET @SQLText = 'INSERT INTO RSYTranPrimary( id, tablename, orctm, currstate ) VALUES( '''+LTRIM(@KeyValue)
+''',''users'', '''+@CurrDT+''' ,''0'')';
EXECUTE(@SQLText)
SET @SQLText = 'INSERT INTO RSYTranDetail( ID, FieldName, oldValue, newValue, FieldMemo ) VALUES(
'''+LTRIM(@KeyValue)+''',''ID'',''Deleted.ID'',''Instered.ID'',''int'')';
EXECUTE(@SQLText)
SET @SQLText = 'INSERT INTO RSYTranDetail( ID, FieldName, oldValue, newValue, FieldMemo ) VALUES(
'''+LTRIM(@KeyValue)+''',''NAME '',''Deleted.NAME'',''Instered.NAME'',''VARCHAR'')';
EXECUTE (@SQLText)
/*
insert into RSYTranPrimary(id, tablename,orctm, currstate)
values(@KeyValue,'users', @CurrDT ,0); insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'userid',d.userid, i.userid,'int' from inserted i,deleted d ;
insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'username',d.username, i.username,'varchar' from inserted i,deleted d ;insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'username',d.time, i.time,'varchar' from inserted i,deleted d ;
*/
--select @count = count(*) from syscolumns where id=object_id('users') select @DELSQL = ' update users
set userid='+LTRIM(i.userid)+',
username='''+i.username+''',
[time]='''+i.[time]+'''
where userid='+LTRIM(d.userid)+'
and username='''+d.username+'''
' from inserted i ,deleted d;select @userid = convert(varchar(500),LTRIM(d.userid)) from users u,deleted d where u.userid = d.userid
print @userid;declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
print @tmpColunName
set @sql = 'select @tmp = '+@tmpColunName+' from users where userid = '+@userid+' ';
exec sp_executesql @sql,N'@tmp varhcar(500) output' ,@tmpValue output
print @tmpValue;
--select @tmpValue = ''+@tmpColunName+'' FROM USERS U,DELETED D WHERE U.USERID = @userid
--print @tmpValue
--print 'select @tmpValue = @tmpColunName FROM USERS U,DELETED D WHERE U.USERID = D.USERID'
IF @tmpValue is not null
begin
print @tmpValue;
end
else
begin
print 'sss';
end --FETCH NEXT FROM column_Cursor into @tmpColunName END;
CLOSE column_Cursor;
DEALLOCATE column_Cursor;
-----这句错了。红色部分
exec sp_executesql @sql,N'@tmp varchar(500) output' ,@tmpValue output
再试试ALTER trigger [update_users_trigger] on [dbo].[users] for update
as
declare @CurrDT varchar(50)
declare @SQLText nvarchar(2000)
declare @DELSQL nvarchar(2000)
declare @KeyValue int
declare @tmpColunName varchar(1000)
declare @tmpValue varchar(500)
declare @sql nvarchar(1000)
declare @tmp varchar(100)
--declare @count intdeclare @userid varchar(500)
begin set @CurrDT = Convert(varchar(20),getdate(),120);
select @KeyValue = currKeyValue from RSYKeyValue where id = 1;
update RSYKeyValue set currKeyValue = currKeyValue + 1 where id = 1;
SET @SQLText = 'INSERT INTO RSYTranPrimary( id, tablename, orctm, currstate ) VALUES( '''+LTRIM(@KeyValue)
+''',''users'', '''+@CurrDT+''' ,''0'')';
EXECUTE(@SQLText)
SET @SQLText = 'INSERT INTO RSYTranDetail( ID, FieldName, oldValue, newValue, FieldMemo ) VALUES(
'''+LTRIM(@KeyValue)+''',''ID'',''Deleted.ID'',''Instered.ID'',''int'')';
EXECUTE(@SQLText)
SET @SQLText = 'INSERT INTO RSYTranDetail( ID, FieldName, oldValue, newValue, FieldMemo ) VALUES(
'''+LTRIM(@KeyValue)+''',''NAME '',''Deleted.NAME'',''Instered.NAME'',''VARCHAR'')';
EXECUTE (@SQLText)
/*
insert into RSYTranPrimary(id, tablename,orctm, currstate)
values(@KeyValue,'users', @CurrDT ,0); insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'userid',d.userid, i.userid,'int' from inserted i,deleted d ;
insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'username',d.username, i.username,'varchar' from inserted i,deleted d ;insert into RSYTranDetail(ID,FieldName,oldValue,newValue,FieldMemo)
select @KeyValue ,'username',d.time, i.time,'varchar' from inserted i,deleted d ;
*/
--select @count = count(*) from syscolumns where id=object_id('users') select @DELSQL = ' update users
set userid='+LTRIM(i.userid)+',
username='''+i.username+''',
[time]='''+i.[time]+'''
where userid='+LTRIM(d.userid)+'
and username='''+d.username+'''
' from inserted i ,deleted d;select @userid = convert(varchar(500),LTRIM(d.userid)) from users u,deleted d where u.userid = d.userid
print @userid;declare column_Cursor CURSOR FOR
select Convert(varchar(1000),name) as column_name from syscolumns where id=object_id('users') ;
open column_Cursor;
fetch NEXT from column_Cursor into @tmpColunName;
WHILE @@FETCH_STATUS = 0
BEGIN
print @tmpColunName
set @sql = 'select @tmp = '+@tmpColunName+' from users where userid = '+@userid+' ';
exec sp_executesql @sql,N'@tmp varchar(500) output' ,@tmpValue output
print @tmpValue;
--select @tmpValue = ''+@tmpColunName+'' FROM USERS U,DELETED D WHERE U.USERID = @userid
--print @tmpValue
--print 'select @tmpValue = @tmpColunName FROM USERS U,DELETED D WHERE U.USERID = D.USERID'
IF @tmpValue is not null
begin
print @tmpValue;
end
else
begin
print 'sss';
end --FETCH NEXT FROM column_Cursor into @tmpColunName END;
CLOSE column_Cursor;
DEALLOCATE column_Cursor;
见23楼。。数据类型写错了啊varchar写成varhcar了