declare @sql varchar(max)
select top 20 @sql = isnull(@sql + '],[' , '') +Manufacturers from dbo.Origin as o1
inner join dbo.OriginUsers as o2 on o1.OriginID=o2.OriginID
left join dbo.OriginRelation as o3 on o1.OriginID=o3.OriginID
inner join userinfo as u on o3.Salers=u.userid
where u.userid=@userID and datediff(dd,o2.date,getdate())=1
group by
Manufacturers
order by sum(usercount) desc set @sql = '[' + @sql + ']'
exec ('select top 30 * from (
select convert(varchar(10),o2.date,120) as date,Manufacturers
,sum(UserCount) as UserCount
from dbo.Origin as o1
inner join dbo.OriginRelation as o3 on o1.OriginID=o3.OriginID
left join dbo.OriginUsers as o2 on o1.OriginID=o2.OriginID
where o3.Salers= '+@userID+'
group by convert(varchar(10),o2.date,120) ,Manufacturers
) a pivot (sum(UserCount) for Manufacturers in (' + @sql + ')) b order by date desc')
代码我用空格分成了2块,现在有一个问题,如果第一块中查询不到东西,那么第二块的代码就执行报错
有没有什么方法能避免呢?只要别报错,返回个空也是可以的
declare @sql varchar(max)
select top 20 @sql = isnull(@sql + '],[' , '') +Manufacturers from dbo.Origin as o1
inner join dbo.OriginUsers as o2 on o1.OriginID=o2.OriginID
left join dbo.OriginRelation as o3 on o1.OriginID=o3.OriginID
inner join userinfo as u on o3.Salers=u.userid
where u.userid=@userID and datediff(dd,o2.date,getdate())=1
group by
Manufacturers
order by sum(usercount) desc
if(isnull(@sql,'') <> '')
begin
set @sql = '[' + @sql + ']'
exec ('select top 30 * from (
select convert(varchar(10),o2.date,120) as date,Manufacturers
,sum(UserCount) as UserCount
from dbo.Origin as o1
inner join dbo.OriginRelation as o3 on o1.OriginID=o3.OriginID
left join dbo.OriginUsers as o2 on o1.OriginID=o2.OriginID
where o3.Salers= '+@userID+'
group by convert(varchar(10),o2.date,120) ,Manufacturers
) a pivot (sum(UserCount) for Manufacturers in (' + @sql + ')) b order by date desc')
exec(@sql)
end