SQLSERVER2000中表变量的应用

原语句如下：

 

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

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

此时，　可以用到SQLSERVER2000中特有的功能Table变量（亦可用临时表，但性能还是有不小的差距）

结果语句如下：

 

 

declare @d2 datetime
set @d2=getdate()
declare @list  Nvarchar(4000)
declare @str Nvarchar(10)
declare @orderid int
declare @tmp int

Declare @TableVar Table (OrderID int  Primary Key ,TestID int)

set @list='28674,28667,28241,355,7210,14646,2164,11891,4519,14671,21788,21816,21817,21108,21196'
set @orderid=0
while (charindex(',',@list)>0)
    begin
   
    set @str=substring(@list,1,charindex(',',@list))
    print @str
    set @tmp= cast(Replace(@str,',','') as int)
    print @tmp
    set @list=substring(@list,len(@str)+1,len(@list)-len(@str)+1)
    ----set @str=Replace(@str,',','')
    set @orderid=@orderid+1
    print @orderid
    Insert Into @TableVar Values (@orderid, @tmp)
    end

-- Select TestID  From @TableVar order by OrderID

select [P_ID] from productinfo p
inner join  @TableVar  a
on p.p_ID=a.TestID
order by a.OrderID

select [语句执行花费时间(毫秒)]=datediff(ms,@d2,getdate())

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/downmoon/archive/2007/02/05/1502600.aspx