SQL表名参数问题

[chenminghong]

set @temp = '##ddd'
set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from [@temp])

--select @sql = @sql +@temp )
) as a order by billdate


set @sql = @sql + ' from kaicheng_TBusRetailDT where style in (select style from '+ @temp+') group by style '
select @sql
exec(@sql)


上面语句提示消息 208，级别 16，状态 1，过程 kaicheng_danpinDateretail，第 66 行
对象名 '@temp' 无效。

[铁歌]

from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT where style in (select style from [@temp]) --关键是这句话错误，忘记了使用引号作字符串拼接了，那么，SQL解析器就把@temp当成一个对象，所以报错

[chenminghong]

select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],' +' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]' +', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]' from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT where style in (select style from [@temp]) 这个一整算的 如果加上引号，上面就无法执行

[chenminghong]

我的需求是 一临时表@temp 要把kaicheng_TBusRetailDT 里面根据临时表的条件过滤，并把kaicheng_TBusRetailDT 里面日期行转列 如果这个没有办法，只能用游标来解决了

[KeepSayingNo]

楼主，临时表是#TEMPTB这样用的，你那个明显是表变量，如果是临时表就像你现在这样写，但是如果是表变量则要用引号和加号来进行连接

[LongRui888]

引用 3 楼 chenminghong 的回复:

我的需求是 一临时表@temp 要把kaicheng_TBusRetailDT 里面根据临时表的条件过滤，并把kaicheng_TBusRetailDT 里面日期行转列 如果这个没有办法，只能用游标来解决了

对了 你的这个 @temp 是个表变量，还是只是一个 变量呀

[發糞塗牆]

DECLARE @a VARCHAR(10)
SET @a='#t'
declare @sql varchar(max)
SELECT @sql='select * into '+@a+' from sys.sysobjects'
EXEC (@sql)

这样试试，不过EXEC完了之后临时表就会消失，所以如果你后续还需要用的话，就不要用临时表

[CAINIAO123]

很不错，试试

[LongRui888]

引用 15 楼 chenminghong 的回复:

create table #temp_123(style varchar(100)) insert into #temp_123 这个固定值，等下2个同时使用，我怕冲突，，我才使用变量，，

改一下，用表变量：

set @temp = '##ddd'

-------------------增加
declare @sql_t nvarchar(1000)

set @sql_t = 'select style from ['+@temp+']'

   
declare @temp_123 table(style varchar(100))

insert into @temp_123
exec(@sql)
----------------------


set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end)  [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end)  [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end)  [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT 
where style in (select style from @temp_123)

[chenminghong]

引用 4 楼 yupeigu 的回复:

[quote=引用 3 楼 chenminghong 的回复:] 我的需求是 一临时表@temp 要把kaicheng_TBusRetailDT 里面根据临时表的条件过滤，并把kaicheng_TBusRetailDT 里面日期行转列 如果这个没有办法，只能用游标来解决了

对了 你的这个 @temp 是个表变量，还是只是一个 变量呀[/quote] 表变量，，，

[LongRui888]

引用 8 楼 chenminghong 的回复:

[quote=引用 4 楼 yupeigu 的回复:] [quote=引用 3 楼 chenminghong 的回复:] 我的需求是 一临时表@temp 要把kaicheng_TBusRetailDT 里面根据临时表的条件过滤，并把kaicheng_TBusRetailDT 里面日期行转列 如果这个没有办法，只能用游标来解决了

对了 你的这个 @temp 是个表变量，还是只是一个 变量呀[/quote] 表变量，，，[/quote] 哦 那上面就不能写成 ： [@temp] 而是只能写成：@temp 另外，还有一个我问题是，在一个普通语句中，表的名称是不能用变量来表示的，也就是不能用@temp来表示，除非是在动态拼接的语句中，也就说你只能把下面的语句，都变成动态语句才行： set @temp = '##ddd' set @sql = 'select style as ' + '款号' select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],' +' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]' +', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]' from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT where style in (select style from [@temp]) --select @sql = @sql +@temp ) ) as a order by billdate set @sql = @sql + ' from kaicheng_TBusRetailDT where style in (select style from '+ @temp+') group by style ' select @sql exec(@sql)

[chenminghong]

我错了，，@temp只是一个变量，，不是值变量，不好意思

[chenminghong]

如果那样鞋，，应该是引号中引号，，，，

[LongRui888]

引用 10 楼 chenminghong 的回复:

我错了，，@temp只是一个变量，，不是值变量，不好意思

哦，呵呵，没事。 对了，这样的话，也有问题，下面这句就错了： where style in (select style from [@temp])

[chenminghong]

是呀，，where style in (select style from [@temp])，，这句话错误，，不知道要如何改正确

[LongRui888]

引用 13 楼 chenminghong 的回复:

是呀，，where style in (select style from [@temp])，，这句话错误，，不知道要如何改正确

改了一下，你试试：

set @temp = '##ddd'

-------------------增加
declare @sql_t nvarchar(1000)

set @sql_t = 'select style from ['+@temp+']'


if OBJECT_ID('tempdb..#temp_123') is not  null
   drop table #temp_123
   
create table #temp_123(style varchar(100))

insert into #temp_123
exec(@sql)
----------------------


set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end)  [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end)  [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end)  [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT 
where style in (select style from #temp_123)

[chenminghong]

create table #temp_123(style varchar(100)) insert into #temp_123 这个固定值，等下2个同时使用，我怕冲突，，我才使用变量，，