SQL表名参数有关问题

2013-12-30

SQL表名参数问题set @temp ##dddset @sql select style as + 款号select @sql @sql + , su

SQL表名参数问题
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' 无效。
[解决办法]

引用:

是呀，，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)

热点排行

SQL Server

SQL表名参数有关问题