關於臨時表
各位新年好！

小弟實際應用中編寫如下兩存儲應過程,每次都得寫字段名及數據類型，總覺不爽，是否有更好的改進方法？還望各位賜教。
1、
Create PROCEDURE Stockfx_1
@where varchar(1000)
as
Create table #temp(dept varchar(10),
  jobno varchar(15),
  subjobno varchar(15),
  flowno varchar(10),
  partno varchar(25),
  stockqty numeric(12,4),
  indate datetime,
  unit nvarchar(10),
  [name] nvarchar(60),
  jobdate datetime,
  model varchar(15),
  flowdesc nvarchar(20))  

declare @cSql varchar(1800)
set @cSql='Insert into #temp
  SELECT b.dept,b.jobno,b.subjobno,b.flowno,b.partno,
b.qty as stockqty,b.indate,a.unit,a.name,c.jobdate,c.model,d.flowdesc 
FROM icdistmap b LEFT JOIN icmt a ON b.partno=a.partno 
LEFT JOIN jobmain c ON b.jobno=c.jobno LEFT JOIN setflow d ON b.flowno=d.flowno 
WHERE '+ @where

exec(@cSql)
if @@error<>0
begin
  truncate table #temp
  drop table #temp
  return
end

Select * From #temp

SELECT distinct a.institem,a.subjobno,a.institem 
  From instmt a inner join #temp b on a.subjobno=b.subjobno

SELECT distinct a.partno as partno1,a.subjobno From jobflow a inner join #temp b on a.subjobno=b.subjobno

truncate table #temp
drop table #temp


2、

Create PROCEDURE wkspeed_2
@model varchar(15)
AS
Declare @temp table(no numeric(2),
  smtno varchar(15),
  jobno varchar(15),  
  flowno varchar(10),
  partno varchar(25),
  item varchar(25),
  fgmt char(1),
  faqty numeric(12,4) )
Insert into @temp 
  Select b.no,b.smtno,b.jobno,b.flowno,b.partno,b.item,b.fgmt,b.faqty 
  From sendmt1 b Left Join jobmain a On b.jobno=a.jobno 
  Where a.model=@Model And a.flagStats='JOR'
Select * from @temp
Select Distinct b.no,b.smtno,b.smtdate,b.fadate,b.outdept,b.indept,b.bumt,b.flagStats 
  From sendmt0 b INNER Join @temp a On b.no=a.no And b.smtno=a.smtno

------解决方案--------------------
啥么意思？没看懂
------解决方案--------------------
可以直接用select col1,col2 into #temp from tab where ...自动创建临时表
------解决方案--------------------
2楼正解
------解决方案--------------------
你创建临时表，然后再查询出里面的数据，为何不直接查询，所用的表上加索引即可
------解决方案--------------------
用wkspeed_2举个例子：

SQL code

2005可用:
with tmp(smtno,jobno,flowno,partno,item,fgmt,faqty)
as
(
     Select   b.no,b.smtno,b.jobno,b.flowno,b.partno,b.item,b.fgmt,b.faqty   
        From   sendmt1   b   Left   Join   jobmain   a   On   b.jobno=a.jobno   
        Where   a.model=@Model   And   a.flagStats='JOR' 
)
Select   
Distinct   b.no,b.smtno,b.smtdate,b.fadate,b.outdept,b.indept,b.bumt,b.flagStats   
        From   sendmt0   b   INNER   Join   tmp   a   On   b.no=a.no   And   b.smtno=a.smtno

2000可用：
        Select   b.no,b.smtno,b.jobno,b.flowno,b.partno,b.item,b.fgmt,b.faqty   
into #
        From   sendmt1   b   Left   Join   jobmain   a   On   b.jobno=a.jobno   
        Where   a.model=@Model   And   a.flagStats='JOR' 
Select   Distinct   b.no,b.smtno,b.smtdate,b.fadate,b.outdept,b.indept,b.bumt,b.flagStats   
        From   sendmt0   b   INNER   Join   #