SQL语句 传自定义表到临时表

SQL不能传数组，也不能传自定义的表。一个变通的办法是转换成一定格式的String以后传。

如输入String s= @'A,1;B,2'

CREATE   PROCEDURE  dbo.ShowId
(
@inputString   varchar ( max )
)
AS
DECLARE    @PointerPrev   int
DECLARE   @PointerCurr    int
DECLARE   @spaceIndex   int
DECLARE   @subString   varchar ( 50 )
DECLARE   @keyWord   varchar ( 50 )
DECLARE   @value   int

Set   @PointerPrev = 0  
set    @PointerCurr = 0
SET   @inputString   =   ' A,1;B,2 '

CREATE   TABLE  # temp
(
    tKeyword  varchar ( 50 ),
    tValue  int
)

begin   transaction  
     SET  NOCOUNT  ON ;
     truncate   table  # temp -- 清空临时表的所有数据和约束
while ( LEN ( @inputString ) > @PointerCurr )
begin
     Set   @PointerCurr = CharIndex  ( ' ; ' , @inputString  , @PointerPrev + 1 )
     if ( @PointerCurr <= 0 )
         break
     else
     begin
         set   @subString = SUBSTRING ( @inputString , @PointerPrev + 1  , @PointerCurr - @PointerPrev - 1 )
         set   @spaceIndex = CharIndex  ( ' , ' , @subString  , 1 )
         set   @keyWord = SUBSTRING ( @subString , 1 , @spaceIndex - 1 )
         set   @value = CAST (  SUBSTRING ( @subString , @spaceIndex + 1 , LEN ( @subString ) - @spaceIndex )  as   int )
         INSERT   INTO  # temp   VALUES ( @keyWord , @value )
         set   @PointerPrev = @PointerCurr ;
     end
end
     set   @subString = SUBSTRING ( @inputString , @PointerPrev + 1  , LEN ( @inputString ) - @PointerPrev )
     set   @spaceIndex = CharIndex  ( ' , ' , @subString  , 1 )
     set   @keyWord = SUBSTRING ( @subString , 1 , @spaceIndex - 1 )
     set   @value = CAST (  SUBSTRING ( @subString , @spaceIndex + 1 , LEN ( @subString ) - @spaceIndex )  as   int )
     INSERT   INTO  # temp   VALUES ( @keyWord , @value )

Set  NoCount  OFF
if   @@error =   0
begin
     commit   transaction
end
else  
begin
     rollback   transaction
end

select   *   from  # temp