向存储过程中传入列表参数

在使用存储过程的时候，有时候会需要传一个列表型的参数，比如我想要查询2013、2014、2015这三年的销售记录。通常的解决办法是将传进来的序列进行字符串拼接，并用逗号进行分隔，比如"2013,2014,2015"，然后就需要一个将字符串进行拆分的function。

CREATE FUNCTION FNC_SPLIT(@MYSTR VARCHAR(500), @DELIMITER CHAR(1))
RETURNS @MYTBL  TABLE (idx smallint, value varchar(8000))
AS 
BEGIN
 DECLARE @RET VARCHAR(500)
 DECLARE @INDEX INT
 DECLARE @COUNTER smallint
 
 --Get the first position of delimiter in the main string
 SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 SET @COUNTER = 0
 
 --Loop if delimiter exists in the main string
 WHILE @INDEX > 0
 BEGIN
  --extract the result substring before the delimiter found
  SET @RET = SUBSTRING(@MYSTR,1, @INDEX-1 )
  --set mainstring right part after the delimiter found
  SET @MYSTR = SUBSTRING(@MYSTR,@INDEX+1 , LEN(@MYSTR) - @INDEX )
  --increase the counter
  SET @COUNTER = @COUNTER  + 1 
  --add the result substring to the table
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @RET)
  --Get the next position of delimiter in the main string
  SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 END
 
 --if no delimiter is found then simply add the mainstring to the table
 IF @INDEX = 0 
 BEGIN
  SET @COUNTER = @COUNTER  + 1
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @MYSTR)
 END 
 RETURN   
END

GO
SELECT value FROM FNC_SPLIT('2013,2014,2015',',')

GO
DROP FUNCTION FNC_SPLIT

运行结果图：

然后将得到的临时表和数据表进行Join就能拿到想要的数据了。

这里介绍一种的方式：OPENXML，需要用到sp_xml_preparedocument和sp_xml_Removedocument这两个存储过程。

DECLARE @xml varchar(max)
DECLARE @doc int
SET @xml ='
<?xml version="1.0" encoding="UTF-8"?> 
<Request>
<Date Year="2013"/>
<Date Year="2014"/>
<Date Year="2015"/>
</Request>'
EXEC sp_xml_preparedocument @doc OUTPUT, @xml
CREATE TABLE #Temp([Year] varchar(100))
INSERT INTO #Temp
SELECT [Year] FROM 
OPENXML (@doc, '/Request/Date', 1)
WITH (Year varchar(100))
EXEC sp_xml_Removedocument @doc
SELECT * FROM #Temp
Drop Table #Temp

运行结果图：

结果和上面是一样的，但是使用XML数据作为参数比拼接字符串要更灵活一些。

比如我传入的XML是这样的：

SET @xml ='
<?xml version="1.0" encoding="UTF-8"?> 
<Request>
<Date Year="2013" Date="10"/>
<Date Year="2014" Date="10"/>
<Date Year="2015" Date="10"/>
</Request>'

我的临时表就可以是这样的：