sql语句中调用变量的问题～

[baggio785]

declare @sql varchar(8000)
declare @t_name varchar(50)
set @t_name = 'downmain'

set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),getdate(),120)+''')=0 ;'
set @sql = @sql + select * from @tablename '

exec(@sql)

提示：必须声明变量 '@tablename'。

请问该如何做呢？如何把变量作为表明来引用呢？

[paoluo]

改為

declare @sql varchar(8000)
declare @t_name varchar(50)
set @t_name = 'downmain'

set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),getdate(),120)+''')=0 ;'
set @sql = @sql +'exec(''select * from ''+@tablename )'
exec(@sql)

[paoluo]

或者


declare @sql Nvarchar(4000)
declare @t_name varchar(50),@tablename varchar(50)
set @t_name = 'downmain'
set @sql = 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),getdate(),120)+''')=0 ;'
EXEC sp_executesql @sql, N'@tablename varchar(50) output', @tablename output
EXEC('select * from '+@tablename)

[ff167]

set @sql = 'declare @tablename varchar(50);'
改成
declare @tablename varchar(50);

download_source是什么?文本值?

[paoluo]

樓上沒有理解樓主語句的意思

[hellowork]

楼主的错误在于select * from @tablename ，其本质是怎样将[表名称字符串]转换成[表名称变量]的问题。
假设@tablename = 'x',将select * from @tablename 展开就是：
select * from 'x'　　　　　　/*这肯定会导致错误的，对象名不能是字符串*/
而应该为select * from x　　　/*这样才是正确的*/
所以会导致错误。
正确的方法是将表名称字符串之前的所有代码也变为字符串并接起来，然后EXEC()该字符串。例如：
EXEC('select * from ' + @tablename)

象paoluo(一天到晚游泳的鱼)写的那样。

[losedxyz]

帮顶

[魔鬼代言人5800]

mark

[baggio785]

基本差不多了

在倒数第四个语句
set @sql = @sql +'Select @t_sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@t_companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@t_date,120)+'')=0'' ;'

如果要得到正确的记过，必须是datediff(d,downdetail_date,'2006-06-21')=0的格式，但是如果使用@t_date变量的话，没办法加''，否则提示必须声明@t_date变量

我的解决办法是把@t_date变量换成@date

最后语句为
set @sql = @sql +'Select @t_sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@t_companyid as varchar(6)) + '' and datediff(d,downdetail_date,'''''+convert(varchar(10),@t_date,120)+''''')=0'' ;'

不知道是否可以使用@t_date变量而达到同样的效果呢？

[paoluo]

再試試

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime

set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
print('@date='''+convert(varchar(10),@date,121)+'''')

set @sql = 'declare @tablename varchar(50) ;'
set @sql = @sql + 'declare @t_sql varchar(1000) ;'
set @sql = @sql + 'declare @t_companyid varchar(1000) ;'
set @sql = @sql + 'set @t_companyid = '+cast(@companyid as varchar(10)) +';'
set @sql = @sql + 'declare @t_date datetime; '
set @sql = @sql + 'set @t_date ='''+convert(varchar(10),@date,121)+''';'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'Select @t_sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@t_companyid as varchar(6)) + '' and datediff(d,downdetail_date,''''''+convert(varchar(10),@t_date,120)+'''''')=0'' ;'
--set @sql = @sql +'exec(@t_sql)'
set @sql = @sql +'print(@t_sql)'
set @sql = @sql +'print(''@t_date=''''''+convert(varchar(10),@t_date,120))+'''''''''
print(@sql)
exec(@sql)

[hellowork]

不用这么麻烦，请楼主将自己在7楼的代码稍微改动一行就可以了。（以“原来如此,谢谢paoluo(一天到晚游泳的鱼) 和 hellowork() ”开头的帖子。）
原代码：
declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime
set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
--修改这行代码就行：set @sql = @sql +'exec(''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@date,120)+'')=0'')'
--修改到此结束
print(@sql)
exec(@sql)
将被注释的代码修改为：
set @sql = @sql +'exec(''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid=' + cast(@companyid as varchar(6)) + ' and datediff(d,downdetail_date,'''''+ convert(varchar(10),@date,120) + ''''')=0' + ''')'

请楼主仔细看一下被修改部分PRINT出来的代码，中心思想是在EXEC里面再动态构造SQL语句。
修改部分的代码生成的字符串类似为：
exec('select Distinct downdetail_songid,downdetail_specialid from ' + @tablename + ' where downdetail_companyid=3 and datediff(d,downdetail_date,''2006-06-21'')=0')

[hellowork]

完整代码为：

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime
set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'exec(''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid=' + cast(@companyid as varchar(6)) + ' and datediff(d,downdetail_date,'''''+ convert(varchar(10),@date,120) + ''''')=0' + ''')'　　/*此行被修改*/
print(@sql)
exec(@sql)

[paoluo]

的確，跟著樓主的思路走複雜了，其實可以直接在我最上面的回復上修改，就可以得到樓主需要的語句。

[hellowork]

使用时请将楼上注释部分　“/*此行被修改*/”　之前的圆角空格（中文空格）清除掉！
重发：
declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime
set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'exec(''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid=' + cast(@companyid as varchar(6)) + ' and datediff(d,downdetail_date,'''''+ convert(varchar(10),@date,120) + ''''')=0' + ''')'
print(@sql)
exec(@sql)

[baggio785]

原来如此,谢谢paoluo(一天到晚游泳的鱼) 和 hellowork()

但是现在有这样一个问题

修改过后的sql语句为

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime

set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()

set @sql = 'declare @tablename varchar(50);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'exec(''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@date,120)+'')=0'')'

print(@sql)

exec(@sql)

print的结果是
declare @tablename varchar(50);
select @tablename=download_source from stat_downmain_200604 where downmain_companyid=1 and datediff(d,downmain_date,'2006-06-21')=0 ;
exec('select Distinct downdetail_songid,downdetail_specialid from ' + @tablename + ' where downdetail_companyid=' + cast(@companyid as varchar(6)) + ' and datediff(d,downdetail_date,'+convert(varchar(10),@date,120)+')=0')

但是当exec的时候就提示'cast' 附近有语法错误。

看了好久也没看出那里有错误，请帮忙指点一下

[paoluo]

try

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime

set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()

set @sql = 'declare @tablename varchar(50,@sql varchar(8000);'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'Select @sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@date,120)+'')=0'';'
set @sql = @sql +'exec(@sql)'

[paoluo]

但是当exec的时候就提示'cast' 附近有语法错误。

原因是在EXEC裡面不能使用cast等函數

[baggio785]

OK，我先试试啊

谢谢paoluo(一天到晚游泳的鱼)

[baggio785]

这个方法可以，：），但是

set @sql = @sql +'Select @sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@date,120)+'')=0'';'

这一句要修改一下，不能在引用@sql、@companyid和@date变量了，需要重新声明一个新变量，完整的sql语句为

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime

set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
print('@date='+convert(varchar(10),@date,121))

set @sql = 'declare @tablename varchar(50) '
set @sql = @sql + 'declare @t_sql varchar(1000) '
set @sql = @sql + 'declare @t_companyid varchar(1000) '
set @sql = @sql + 'set @t_companyid = '+cast(@companyid as varchar(10)) +' '
set @sql = @sql + 'declare @t_date datetime '
set @sql = @sql + 'set @t_date = '+convert(varchar(10),@date,121) +' '
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 '
set @sql = @sql +'Select @t_sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@t_companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@t_date,120)+'')=0'' '
--set @sql = @sql +'exec(@t_sql)'
set @sql = @sql +'print(@t_sql)'
set @sql = @sql +'print(''@t_date=''+convert(varchar(10),@t_date,120))'

print(@sql)
exec(@sql)

但是@t_date的值是1905-06-03，这是为什么呢

迷惑啊～：（

paoluo(一天到晚游泳的鱼)，还要麻烦你再帮我看看啊

[paoluo]

try

declare @sql varchar(8000)
declare @t_name varchar(50)
declare @companyid int
declare @date datetime

set @t_name = 'stat_downmain_200604'
set @companyid = 1
set @date = '2006-06-21'--getdate()
print('@date='''+convert(varchar(10),@date,121)+'''')

set @sql = 'declare @tablename varchar(50) ;'
set @sql = @sql + 'declare @t_sql varchar(1000) ;'
set @sql = @sql + 'declare @t_companyid varchar(1000) ;'
set @sql = @sql + 'set @t_companyid = '+cast(@companyid as varchar(10)) +';'
set @sql = @sql + 'declare @t_date datetime '
set @sql = @sql + 'set @t_date ='''+convert(varchar(10),@date,121)+''';'
set @sql = @sql + 'select @tablename=download_source from '+@t_name+' where downmain_companyid=1 and datediff(d,downmain_date,'''+convert(varchar(10),@date,120)+''')=0 ;'
set @sql = @sql +'Select @t_sql =''select Distinct downdetail_songid,downdetail_specialid from '' + @tablename + '' where downdetail_companyid='' + cast(@t_companyid as varchar(6)) + '' and datediff(d,downdetail_date,''+convert(varchar(10),@t_date,120)+'')=0'' ;'
--set @sql = @sql +'exec(@t_sql)'
set @sql = @sql +'print(@t_sql)'
set @sql = @sql +'print(''@t_date=''+convert(varchar(10),@t_date,120))'

print(@sql)
exec(@sql)

[baggio785]

实在不好意思,问题解决了,这么晚才结分,前两天的网络不好,不能上网,再次感谢~~