SQLSERVER2000中表变量的应用

原语句如下

 

1 declare @d datetime 
2 set @d=getdate() 
3 SELECT    [P_ID]  FROM     [ProductInfo]  
4 WHERE [P_ID] IN 
5 (28674,28667,28241,355,7210,14646,2164,11891,4519,14671,21788,21816,21817,21108,21196)
6 select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())


现要求按括号中的ID顺序输出结果

此时，　可以用到SQLSERVER2000　以上版本中特有的表变量(table)（亦可用临时表，但性能还是有不小的差距）

结果语句如下：

 1 declare @d2 datetime
 2 set @d2=getdate() 
 3 declare @list  Nvarchar(4000)
 4 declare @str Nvarchar(10)
 5 declare @orderid int
 6 declare @tmp int
 7 
 8 Declare @TableVar Table (OrderID int  Primary Key ,TestID int)
 9 
10 set @list='28674,28667,28241,355,7210,14646,2164,11891,4519,14671,21788,21816,21817,21108,21196'
11 set @orderid=0
12 while (charindex(',',@list)>0)
13     begin 
14     
15     set @str=substring(@list,1,charindex(',',@list))
16     print @str
17     set @tmp= cast(Replace(@str,',','') as int)
18     print @tmp
19     set @list=substring(@list,len(@str)+1,len(@list)-len(@str)+1)
20     ----set @str=Replace(@str,',','')
21     set @orderid=@orderid+1
22     print @orderid
23     Insert Into @TableVar Values (@orderid, @tmp)
24     end
25 
26 -- Select TestID  From @TableVar order by OrderID
27 
28 
29 select [P_ID] from productinfo p 
30 inner join  @TableVar  a 
31 on p.p_ID=a.TestID
32 order by a.OrderID
33 
34 select [语句执行花费时间(毫秒)]=datediff(ms,@d2,getdate())

 


posted @ 2007-12-29 13:46  邀月  阅读(618)  评论(0)  编辑  收藏  举报