如何使用print打印sql server存储过程脚本

edb_zhangxx 2012-03-30 10:39:52

USE [wfp]
GO
/****** Object: StoredProcedure [dbo].[pr_库存查看] Script Date: 03/30/2012 09:37:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- exec pr_库存查看 '#库房#','#产品编号#','#条码#','#品名#','#厂家货号#','#库位#','#产品分类#','','#WFPUSER#','#AGT_TRAD.#'

ALTER proc [dbo].[pr_库存查看]
@库房 varchar(30)
,@产品编号 varchar(20)
,@条码 varchar(20)
,@品名 varchar(50)
,@厂家货号 varchar(20)
,@库位 varchar(20)
,@产品分类 varchar(20)
,@type varchar(1)
,@WFPUSER varchar(20)
,@dbname varchar(20)
as

if @dbname='edb_a59034.'
begin --
exec pr_库存查看_qgg @库房,@产品编号,@条码,@品名,@厂家货号,@库位,@产品分类,@type,@WFPUSER,@dbname
return
end

set transaction isolation level read uncommitted

exec('if object_id(''tempdb..['+@WFPUSER+'库存查看]'') is not null drop table tempdb..['+@WFPUSER+'库存查看]
if object_id(''tempdb..['+@WFPUSER+'库存查看在途数量]'') is not null drop table tempdb..['+@WFPUSER+'库存查看在途数量]
if object_id(''tempdb..['+@WFPUSER+'库存查看实物库存]'') is not null drop table tempdb..['+@WFPUSER+'库存查看实物库存]
if object_id(''tempdb..['+@WFPUSER+'预计到货时间]'') is not null drop table tempdb..['+@WFPUSER+'预计到货时间]
if object_id(''tempdb..['+@WFPUSER+'预计到货时间_temp]'') is not null drop table tempdb..['+@WFPUSER+'预计到货时间_temp]')

declare @sql varchar(8000),@sql2 varchar(5000)
set @sql='declare @sql varchar(500)
set @sql='''+@库房+'''
select @sql=''
if object_id(''''tempdb..['+@WFPUSER+'temp_kf]'''') is not null drop table tempdb..['+@WFPUSER+'temp_kf]
select cast(null as varchar(255)) 库房编码 into tempdb..['+@WFPUSER+'temp_kf]
insert into tempdb..['+@WFPUSER+'temp_kf](库房编码) select ''''''+replace(@sql,'','','''''' union all select '''''')+''''''''
exec(@sql)

delete from tempdb..['+@WFPUSER+'temp_kf] where isnull(库房编码,'''')=''''
declare @第一个库房 int
set @第一个库房=(select top 1 库房编码 from tempdb..['+@WFPUSER+'temp_kf])

select a.AC0011 产品编号,e.objjc 品牌,b.EC0123 规格,a.EC0104 品名
,d.AD0506 产品分类
,cast(b.AD0001 as varchar) 产品重量
,b.AC2037 条形码,b.AC2039 备用条码,a.AC0012 厂家货号,g.objjc 库房,c.objexplain 库位,
case when @第一个库房=f.库房编码 then case f.库房编码 when 1 then b.AI0001
when 2 then b.AI0002
when 3 then b.AI0003
when 4 then b.AI0004
when 5 then b.AI0005
when 6 then b.AI0006
when 7 then b.AI0007
when 8 then b.AI0008
when 9 then b.AI0009
when 10 then b.AI0010 end -
isnull(b.AI0011,0) else 0 end 图片库存,
case f.库房编码
when 1 then b.AI0001
when 2 then b.AI0002
when 3 then b.AI0003
when 4 then b.AI0004
when 5 then b.AI0005
when 6 then b.AI0006
when 7 then b.AI0007
when 8 then b.AI0008
when 9 then b.AI0009
when 10 then b.AI0010 end 理论库存
, cast(null as varchar(200)) [预计到货时间(数量)],
0 总在途数量,0 实物库存,b.ai0012 未付款已拍数量,
c.ac0016 库房编码,a.AD0506 品牌编码
,a.AC0013 产品简介
,isnull(b.ac0060,'''') 产品备注
,isnull(b.AI0018,0) 附加消耗
into tempdb..['+@WFPUSER+'库存查看]
from '+@dbname+'.WFPUSER_A0301 a
join '+@dbname+'.WFPUSER_A0304 b on a.AC0011=b.AC0011 and a.AD0506=b.AD0506 and isnull(b.AD0522,'''')<>''停用''
and (left('''+@WFPUSER+''' ,5)<>''edb_g''
or left('''+@WFPUSER+''' ,5)=''edb_g'' and a.AD2503 =(select top 1 objname from '+@dbname+'.WFPCODE_AD503 where username='''+@WFPUSER+'''))

join '+@dbname+'.WFPUSER_A0308 c on c.objorder=b.objid
join '+@dbname+'.WFPUSER_A0301 d on a.parentid=d.objid --产品分类
left join '+@dbname+'.WFPUSER_A0301 d1 on d.parentid=d1.objid
join '+@dbname+'.WFPCODE_AD502 e on a.AD0506=e.objname
join tempdb..['+@WFPUSER+'temp_kf] f on f.库房编码=c.ac0016
join '+@dbname+'.wfpuser_a0401 g on f.库房编码=g.objorder
left join '+@dbname+'.WFPUSER_A0310 h on b.ac0011=h.ac0011 and b.ad0506=h.ad0506 and b.ec0123=h.ec0123 and f.库房编码=h.ac0016
where a.objid>0
and (b.AC0011 like ''%'+@产品编号+'%'' or isnull('''+@产品编号+''','''')='''')
and (b.AC2037 like ''%'+@条码+'%'' or b.AC2039 like ''%'+@条码+'%'' or isnull('''+@条码+''','''')='''')
and (a.EC0104 like ''%'+@品名+'%'' or isnull('''+@品名+''','''')='''')
and (a.AC0012 like ''%'+@厂家货号+'%'' or isnull('''+@厂家货号+''','''')='''')
and (c.objexplain like ''%'+@库位+'%'' or isnull('''+@库位+''','''')='''')
and (d.objid ='''+@产品分类+''' or d1.objid='''+@产品分类+''' or isnull('''+@产品分类+''',''1'')=''1''or isnull('''+@产品分类+''','''')='''')

--and(

-- charindex('''+@条码+''',b.AC2037)>0
--or charindex('''+@条码+''',b.AC2039)>0
--or charindex('''+@品名+''',a.EC0104)>0
--or charindex('''+@厂家货号+''',a.AC0012)>0
--or charindex('''+@库位+''',c.objexplain)>0
--or charindex('''+@产品分类+''',d.objid)>0
--or charindex('''+@产品分类+''',d1.objid)>0)

--在途库存
select BB.AD0506,BB.EC0123,BB.AC0011,max(aa.ac0016)ac0016,sum(case when aa.AQ0005 is null then BB.AI0014 else 0 end) 总在途数量
into tempdb..['+@WFPUSER+'库存查看在途数量]
FROM '+@dbname+'.WFPUSER_A0412 AA
join tempdb..['+@WFPUSER+'库存查看]d on aa.ac0016=d.库房编码
join '+@dbname+'.WFPUSER_A0416 BB ON AA.AC0025=BB.AC0025 and BB.AD0506=d.品牌编码 and BB.EC0123=d.规格 and BB.AC0011=d.产品编号
join '+@dbname+'.WFPUSER_A0419 cc on aa.AC2048=cc.AC0025
join '+@dbname+'.WFPUSER_A0418 dd on cc.AC0025=dd.AC0025
and BB.AD0506=cc.AD0506 and BB.EC0123=cc.EC0123 and BB.AC0011=cc.AC0011 and aa.ac0016=cc.ac0016
where aa.objid<>0
and isnull(aa.AC2048,'''')<>''''
group by BB.AD0506,BB.EC0123,BB.AC0011

--实物库存
if object_id (''tempdb..#实物库存临时表'') is not null drop table #实物库存临时表
select b.AD0506 ad0506,b.AC0011 ac0011,b.EC0123 ec0123,sum(EI0734) EI0734,A.AC0018 AC0018,a.AC0016 AC0016
INTO #实物库存临时表
from '+@dbname+'.WFPUSER_A0404 a
join '+@dbname+'.WFPUSER_A0408 b on a.AC0018=b.AC0018 and a.objid<>0
and isnull(a.AD0512,''1'') in(''1'',''11'')
AND isnull(a.AD0511,''0'')=''1''
join tempdb..['+@WFPUSER+'temp_kf] d on d.库房编码=a.ac0016
where a.objid<>0
group by b.AD0506,b.AC0011,b.EC0123,A.AC0018,a.ac0016

select b.AC0016,b.AD0506 ad0506,b.AC0011 ac0011,b.EC0123 ec0123,sum(EI0734) 未发货数量
into tempdb..['+@WFPUSER+'库存查看实物库存]
from
(select b.AC0016,b.AD0506 ad0506,b.AC0011 ac0011,b.EC0123 ec0123,sum(EI0734) EI0734
from #实物库存临时表 b
group by b.AD0506,b.AC0011,b.EC0123,b.ac0016
union all
select
b.AC0016,t1.AD0507 ad0506 ,t1.AC0536 ac0011,t1.AC0537 ec0123 ,sum(t1.ai0014) EI0734
FROM #实物库存临时表 B
join '+@dbname+'.WFPUSER_A0430 t1 on b.AC0018=t1.AC0018
and b.AD0506=t1.AD0506 and b.AC0011=t1.AC0011 and b.EC0123=t1.EC0123
and ad0526=''销售出库''
group by t1.AD0507,t1.AC0536,t1.AC0537,b.ac0016) b
group by b.AD0506,b.AC0011,b.EC0123,b.ac0016

--预计到货时间
select c.品牌编码,c.规格,c.产品编号,c.库房编码
,max(b.AQ2050) 预计到货时间
,sum(b.AI0015)-isnull(sum(b.AI0035),0)-isnull(sum(case when d.ac0025 is not null then e.AI0014 end),0) 采购数量
,cast('''' as varchar(200)) 预计到货时间数量
into tempdb..['+@WFPUSER+'预计到货时间_temp]
from '+@dbname+'.WFPUSER_A0418 a
join '+@dbname+'.WFPUSER_A0419 b on a.AC0025=b.AC0025
left join '+@dbname+'.WFPUSER_A0416 e on b.ac0011=e.ac0011 and b.ec0123=e.ec0123 and b.ad0506=e.ad0506
left join '+@dbname+'.WFPUSER_A0412 d on a.AC0025=d.AC2048 and d.AC0025=e.AC0025
join tempdb..['+@WFPUSER+'库存查看] c on c.品牌编码=b.AD0506 and c.规格=b.EC0123 and c.产品编号=b.AC0011 and c.库房编码=a.ac0016
where a.objid<>0 and isnull(a.AQ0002,'''')<>'''' and isnull(d.AQ0005,''1900-01-01'')<>(case when d.objid is not null then '''' else ''1901-01-01'' end)
and isnull(a.aq2051,''1900-01-01'')=''1900-01-01''
group by a.AC0025,c.品牌编码,c.规格,c.产品编号,c.库房编码
having sum(b.AI0015)-isnull(sum(b.AI0035),0)-isnull(sum(case when d.ac0025 is not null then e.AI0014 end),0)>0
order by c.品牌编码,c.规格,c.产品编号,c.库房编码,预计到货时间,采购数量

'
set @sql2='
declare @品牌编码 varchar(40),@规格 varchar(100),@产品编号 varchar(40),@库房编码 varchar(10),@预计到货时间数量 varchar(400)
select @品牌编码='''',@规格='''',@产品编号='''',@库房编码='''',@预计到货时间数量=''''
update tempdb..['+@WFPUSER+'预计到货时间_temp]
set @预计到货时间数量=cast(case when 品牌编码=@品牌编码 and 规格=@规格 and 产品编号=@产品编号 and 库房编码=case when '''+@type+'''=''0'' then @库房编码 else 库房编码 end
then @预计到货时间数量+isnull(convert(char(10),预计到货时间,120),''到货时间无'')+''(''+cast(采购数量 as varchar)+'')''+'';''
else isnull(convert(char(10),预计到货时间,120),''到货时间无'')+''(''+cast(采购数量 as varchar)+'')''+'';''
end as varchar(200))
,预计到货时间数量=@预计到货时间数量
,@品牌编码=品牌编码,@规格=规格,@产品编号=产品编号,@库房编码=库房编码

select 品牌编码,规格,产品编号,库房编码,max(预计到货时间数量)预计到货时间数量
into tempdb..['+@WFPUSER+'预计到货时间]
from tempdb..['+@WFPUSER+'预计到货时间_temp]
group by 品牌编码,规格,产品编号,库房编码

update tempdb..['+@WFPUSER+'库存查看] set 总在途数量=isnull(b.总在途数量,0),实物库存=isnull(理论库存,0)+isnull(未发货数量,0),[预计到货时间(数量)]=d.预计到货时间数量
from tempdb..['+@WFPUSER+'库存查看] a
left join tempdb..['+@WFPUSER+'库存查看在途数量] b on b.AD0506=a.品牌编码 and b.EC0123=a.规格 and b.AC0011=a.产品编号 and b.ac0016=a.库房编码
left join tempdb..['+@WFPUSER+'库存查看实物库存] c on a.品牌编码=c.AD0506 and a.规格=c.EC0123 and a.产品编号=c.AC0011 and c.ac0016=a.库房编码
left join tempdb..['+@WFPUSER+'预计到货时间] d on a.品牌编码=d.品牌编码 and a.规格=d.规格 and a.产品编号=d.产品编号 and d.库房编码=a.库房编码

if('''+@type+'''=''0'')
begin
update tempdb..['+@WFPUSER+'库存查看] set 图片库存=图片库存-附加消耗
select * from tempdb..['+@WFPUSER+'库存查看]
end

if('''+@type+'''=''1'')
begin
select 产品编号,品牌,规格,max(品名)品名,max(产品分类)产品分类,max(产品重量)产品重量,max(条形码)条形码
,max(备用条码)备用条码,max(厂家货号)厂家货号,sum(图片库存)-max(附加消耗) 图片库存,sum(理论库存)理论库存,max([预计到货时间(数量)])[预计到货时间(数量)],sum(总在途数量)总在途数量
,sum(实物库存)实物库存,sum(未付款已拍数量)未付款已拍数量
,max(产品简介) as 产品简介
,max(产品备注) as 产品备注
from tempdb..['+@WFPUSER+'库存查看]
group by 品牌,规格,产品编号
enda
'
如何将上面所写的存储过程Print 输出来？大神们求解！

...全文