SQLSERVER 存储过程传递数组参数

方法一：利用字符串截取拆分数组字符串

 

脚本

--  =============================================
--  Author:<lx>
--  Create date: <2010-11-26>
--  Description:    <存储过程数组参数>
--  方法一:利用字符串截取拆分数组字符串
--  =============================================
CREATE   PROCEDURE  SP_ParameterArray 
  @UserName   VARCHAR ( 10 ),
  @StrArray   VARCHAR ( 800 )
AS
DECLARE   @P   INT
DECLARE   @C   INT
DECLARE   @Nums   INT
SET   @P = 1  
SET   @C = 1
BEGIN   TRANSACTION      
Set  NOCOUNT  ON
  CREATE   TABLE  #t_info
 (
   UserName  VARCHAR ( 10 ),
   Nums  INT
 )
  SET   @C = CHARINDEX ( ' , ' , @StrArray , @P + 1 )
  SET   @Nums = CAST ( SUBSTRING ( @StrArray , @P , @C - @P )  AS   INT )
  INSERT   INTO  #t_info(UserName,Nums)  VALUES ( @UserName , @Nums )
  SET   @P = @C
  WHILE  ( @P + 1 <   LEN ( @StrArray ))
   BEGIN
    SET   @C = CHARINDEX ( ' , ' , @StrArray , @P + 1 )
    IF ( @C >   0 )
     BEGIN
      SET   @Nums = CAST  ( SUBSTRING ( @StrArray , @P + 1 , @C - @P - 1 )  AS   INT )
      INSERT   INTO  #t_info(UserName,Nums)  VALUES ( @UserName , @Nums )
      SET   @P = @C
     END
    ELSE
     BREAK   
   END
SET   @Nums = CAST ( SUBSTRING ( @StrArray , @P + 1 , LEN ( @StrArray ) - @P )  AS   INT )
INSERT   INTO  #t_info(UserName,Nums)  VALUES ( @UserName , @Nums )
SELECT   *   FROM  #t_info
DROP   TABLE  #t_info
SET  NOCOUNT  OFF
IF   @@ERROR = 0
  BEGIN
   COMMIT   TRANSACTION
  END
ELSE
  BEGIN
    ROLLBACK   TRANSACTION
  END
GO

 

方法二：利用OpenXML 效率更高

脚本

--  =============================================
--  Author:<lx>
--  Create date: <2010-11-26>
--  Description:    <存储过程数组参数>
--  方法二:利用OpenXML 效率更高
--  @XML_Array='<Array><PA Nums="1"></PA><PA Nums="2"></PA><PA Nums="3"></PA><PA Nums="4"></PA></Array>'
--  =============================================
CREATE   PROCEDURE   [ dbo ] . [ SP_ParameterArray2 ]  
  @UserName   VARCHAR ( 10 ),
  @XML_Array   nvarchar ( 500 )
AS
BEGIN  
IF  ( @XML_Array   IS   NULL   OR   LEN ( LTRIM ( RTRIM ( @XML_Array )))  =   0 ) 
  RETURN  
CREATE   TABLE  #t_info(UserName  VARCHAR ( 10 ),Nums  INT )
DECLARE   @IdHandel   INT
EXEC  sp_xml_preparedocument  @IdHandel  OUTPUT,  @XML_Array
INSERT   INTO  #t_info(UserName,Nums)
SELECT   @UserName ,N.Nums 
FROM  OPENXML( @IdHandel , ' /Array/PA ' )  WITH (Nums  INT )  AS  N
WHERE  N. [ Nums ]   IS   NOT   NULL
EXEC  sp_xml_removedocument  @IdHandel
SELECT   *   FROM  #t_info
DROP   TABLE  #t_info
END

 

 

转载于:https://www.cnblogs.com/love_study/archive/2010/11/26/1888580.html