SQL笔记

1.  exec sp_executesql 'SQL',如果SQL要用到临时表，需要用全局临时表##Table，因为执行SQL语句的Session与当前执行exec不一样。

 

if object_id('tempdb..##loadkeytmp') is not null drop table ##loadkeytmp
declare   @string   varchar(1000)
set   @string= 'aaa|777|999|888 '
set   @string= 'select t.* into ##loadkeytmp from (select '''+replace(@string, '|', ''' as str union all select ''')+ ''') t'
exec(@string)
select * from ##loadkeytmp
drop table ##loadkeytmp

 

2. 一列数据有多行，合并成一列一行。

ALTER Function [dbo].[GetMultiLocForEA](@Sku char(20),@Lottable02 char(20),@LogicalLocation char(20))
Returns varchar(max)
As
Begin
declare @sql varchar(max)
declare @ret varchar(max)
select @sql = isnull(@sql + ';' , '') + rtrim(Loc)+ '' from
(
select top 5 a.Loc,sum(a.qty-a.qtyallocated-a.qtypicked) as qty
from idscn..lotxlocxid a(nolock)
inner join idscn..loc b(nolock) on a.loc=b.loc
inner join idscn..lotattribute c(nolock) on a.lot=c.lot
where a.storerkey='18301' and a.sku=@Sku and c.lottable02=@Lottable02
and (a.qty-a.qtyallocated-a.qtypicked)>0 and b.status='OK' and b.LogicalLocation like '%'+rtrim(@LogicalLocation)+'%'
group by a.loc
) t 
order by t.qty
select @ret=@sql
return isnull(@ret,'')
End