declare @m1 varchar(10)
declare @m varchar(18)
declare @n varchar(18)
declare @n1 varchar(10)
declare @c int
declare @i int
declare @t char(6)
declare @f char(2)
declare @sql varchar(500)
set @f=''''
set @t=convert(char(6),getdate()-1,12)
set @i = 1
set @m1 = convert(varchar(10), dateadd(dd, -@i, getdate()), 120)
set @n1 =convert(varchar(10), dateadd(dd, 1, @m1), 120)
set @m=@f+@m1+@f
set @n=@f+@n1+@f
set @sql='set @c=(select count(*) as total from dbo.temp
where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67)))'
exec(@sql)
insert into dbo.dd values (@m1, @c)select *
from dbo.dd with (nolock)
order by date desc
go老是报错说@c未定义.好痛苦啊哪位帮忙解答下
declare @m varchar(18)
declare @n varchar(18)
declare @n1 varchar(10)
declare @c int
declare @i int
declare @t char(6)
declare @f char(2)
declare @sql varchar(500)
set @f=''''
set @t=convert(char(6),getdate()-1,12)
set @i = 1
set @m1 = convert(varchar(10), dateadd(dd, -@i, getdate()), 120)
set @n1 =convert(varchar(10), dateadd(dd, 1, @m1), 120)
set @m=@f+@m1+@f
set @n=@f+@n1+@f
set @sql='set @c=(select count(*) as total from dbo.temp
where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67)))'
exec(@sql)
insert into dbo.dd values (@m1, @c)select *
from dbo.dd with (nolock)
order by date desc
go老是报错说@c未定义.好痛苦啊哪位帮忙解答下
declare @m varchar(18)
declare @n varchar(18)
declare @n1 varchar(10)
declare @c int
declare @i int
declare @t char(6)
declare @f char(2)
declare @sql nvarchar(4000)
set @f=''''
set @t=convert(char(6),getdate()-1,12)
set @i = 1
set @m1 = convert(varchar(10), dateadd(dd, -@i, getdate()), 120)
set @n1 =convert(varchar(10), dateadd(dd, 1, @m1), 120)
set @m=@f+@m1+@f
set @n=@f+@n1+@fset @sql=N'select @c=count(*) as total from dbo.temp where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67))'exec sp_executesql @sql,'@c int output',@c outputinsert into dbo.dd values (@m1, @c)select * from dbo.dd with (nolock) order by date desc
go
这个例子执行 roy_check 存储过程,传递三个参数。第三个参数 @pc 是输出参数。过程执行完后,返回变量可以从变量 @percent 得到。说明 roy_check 存储过程只是用作举例,pubs 数据库中并没有此过程。
DECLARE @percent int
EXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUT
SET Percent = @percent
declare @m varchar(18)
declare @n varchar(18)
declare @n1 varchar(10)
declare @c int
declare @i int
declare @t char(6)
declare @f char(2)
declare @sql nvarchar(4000)
set @f=''''
set @t=convert(char(6),getdate()-1,12)
set @i = 1
set @m1 = convert(varchar(10), dateadd(dd, -@i, getdate()), 120)
set @n1 =convert(varchar(10), dateadd(dd, 1, @m1), 120)
set @m=@f+@m1+@f
set @n=@f+@n1+@fset @sql=N'select @c=count(*) from dbo.temp where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67))'exec sp_executesql @sql,'@c int output',@c outputinsert into dbo.dd values (@m1, @c)select * from dbo.dd with (nolock) order by date desc
go
where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67)))'这句错了,有问题。 @sql这个东东写的不对,再说了,好想没有什么意义啊! 直接写下面的语句不就可以了么
set @c = '(select count(*) as total from dbo.temp
where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67)))'
where date between '+@m+' and '+@n+' and aa in (select distinct(aa) from t1_'+@t+' where cc not in (0,22,45,67)))'
exec(@sql)
你这样写是不行的
应该将exec(@sql)改为
sp_executesql @sql,N'@c int output',@c output
我以前就是这样写的,但是有错,@t未定义.所以才用后来的方法的.
谢谢各位啦
libin_ftsafe 的方法是对的,但改exec sp_executesql @sql,N'@c int output',@c output
今天头比较晕.
结贴啦. ^m^