使用sp_executesql,参考:
declare @tt int
declare @ss nvarchar(1000)
set @ss=N'select @tt=count(*) from table3'
exec sp_executesql @ss ,N'@tt int output',@tt output
declare @tt int
declare @ss nvarchar(1000)
set @ss=N'select @tt=count(*) from table3'
exec sp_executesql @ss ,N'@tt int output',@tt output
declare @tbl varchar(10), @sql Nvarchar(1000), @count intselect @tbl='tablename'
set @sql ='select @count=COUNT(*) from [' +@tbl+ ']'
exec sp_executesql @sql, N'@count int output',@count OUTPUT
select @count
BEGIN
set @query='declare @total int '
set @query=@query + 'select @total=sum(['+@fieldname+']) from '+@tablename+' where 地区编码 !="'+@dwbm+'" and 地区编码 like "'+@dwbm+'%" '
set @query=@query + ' update '+@tablename+' set ['+@fieldname+']=@total where 地区编码="'+@dwbm+'"'
EXECUTE(@query)
FETCH NEXT FROM field_cursor into @fieldname
END
declare @tcnt int
declare @paras varchar(100)
DECLARE @SQLString NVARCHAR(500)
set @paras ='1,3,5'
set @sqlstring=N'select @cnt=count(*) from sysobjects where id in ('+@paras+')'set @tcnt=0
execute sp_executesql
@sqlstring,
N'@cnt int output',
@cnt=@tcnt output
select @tcnt
select * from sysobjectsCreate Procedure GetMaxID
@TableName varchar(100), @ID int output
as
begin
declare @sql nvarchar(1000)
set @sql='select @ID = count(ID) from '+@TableName
exec sp_executesql @sql,N'@id int output',@id output
end
(
@tablename varchar(50),
@dwbm varchar(8)
)
AS
DECLARE @fieldname varchar(100)
DECLARE @query varchar(2000)
DECLARE @total int
Declare @Output VarChar(50) --定义一参数.DECLARE field_cursor CURSOR FOR
SELECT 字段名 FROM 查询字段表
WHERE 表名=@tablename and 字段类型='数值型'OPEN field_cursor
FETCH NEXT FROM field_cursor into @fieldname
WHILE @@FETCH_STATUS = 0
BEGIN
set @query='select @out=sum('+@fieldname+') from '+@tablename
+' where 地区编码 !="'+@dwbm+'" and 地区编码 like "'+@dwbm+'%" ' Set @Output='@out int output'
exec sp_executesql @query,@output,@total output --把统计结果输出到@total变量 set @query='update '+@tablename+' set ['+@fieldname+']='+str(@total)+' where 地区编码="'+@dwbm+'"'
EXECUTE(@query)
FETCH NEXT FROM field_cursor into @fieldname
END
CLOSE field_cursor
DEALLOCATE field_cursor
GO