以下这一段应该如怎么样写成存储过程???
if object_id('tempdb..#TempSwitch') is not null drop table #TempSwitch
create table #TempSwitch(PtypeId varchar(25))
insert into #TempSwitch(PtypeId) SELECT DISTINCT PtypeId FROM GoodsStocks WHERE (KtypeId = '0000500001') and ptypeid not in(SELECT DISTINCT PtypeId AS ID号 FROM GoodsStocks WHERE (KtypeId = '00004')) if exists (select * from sysobjects where id =object_id(N'[dbo].[仓库比较表]') and OBJECTPROPERTY(id, N'IsUserTable')=1)
drop table [dbo].[仓库比较表] create table 仓库比较表
(
Usercode varchar(25),
Fullname varchar(100),
Standard varchar(150),
area varchar(255)
)
insert into 仓库比较表(Usercode,Fullname,Standard,area) SELECT dbo.ptype.usercode as 编码,dbo.ptype.Fullname as 品名,dbo.ptype.standard as 规格,dbo.ptype.area as 产地
FROM ptype INNER JOIN #TempSwitch ON ptype.typeId = #TempSwitch.PtypeId select * from 仓库比较表
if object_id('tempdb..#TempSwitch') is not null drop table #TempSwitch
create table #TempSwitch(PtypeId varchar(25))
insert into #TempSwitch(PtypeId) SELECT DISTINCT PtypeId FROM GoodsStocks WHERE (KtypeId = '0000500001') and ptypeid not in(SELECT DISTINCT PtypeId AS ID号 FROM GoodsStocks WHERE (KtypeId = '00004')) if exists (select * from sysobjects where id =object_id(N'[dbo].[仓库比较表]') and OBJECTPROPERTY(id, N'IsUserTable')=1)
drop table [dbo].[仓库比较表] create table 仓库比较表
(
Usercode varchar(25),
Fullname varchar(100),
Standard varchar(150),
area varchar(255)
)
insert into 仓库比较表(Usercode,Fullname,Standard,area) SELECT dbo.ptype.usercode as 编码,dbo.ptype.Fullname as 品名,dbo.ptype.standard as 规格,dbo.ptype.area as 产地
FROM ptype INNER JOIN #TempSwitch ON ptype.typeId = #TempSwitch.PtypeId select * from 仓库比较表
'0000500001 '
作为变量传参
create proc test
@KtypeId nvarchar(10),-- '0000500001 '
@KtypeId2 nvarchar(5)--'00004 '
asif object_id( 'tempdb..#TempSwitch ') is not null drop table #TempSwitch
create table #TempSwitch(PtypeId varchar(25))
insert into #TempSwitch(PtypeId)
SELECT
DISTINCT PtypeId
FROM
GoodsStocks
WHERE
(KtypeId = @KtypeId) and
ptypeid not in
(SELECT DISTINCT PtypeId AS ID号 FROM GoodsStocks WHERE (KtypeId = @KtypeId2)) if exists (select * from sysobjects where id =object_id(N '[dbo].[仓库比较表] ') and OBJECTPROPERTY(id, N 'IsUserTable ')=1)
drop table [dbo].[仓库比较表] exec('create table 仓库比较表
( Usercode varchar(25),
Fullname varchar(100),
Standard varchar(150),
area varchar(255)
)
insert into 仓库比较表(Usercode,Fullname,Standard,area)
SELECT
dbo.ptype.usercode as 编码,dbo.ptype.Fullname as 品名,dbo.ptype.standard as 规格,dbo.ptype.area as 产地
FROM
ptype
INNER JOIN
#TempSwitch ON ptype.typeId = #TempSwitch.PtypeId
select * from 仓库比较表')