传多个值放到存储过程的一个变量中的处理方法

Harold_xlp

CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
     RowId int Not Null IDENTITY(1,1) PRIMARY KEY
    ,Value nvarchar(255) Not Null
)
AS
BEGIN
    DECLARE  @startPos smallint
            ,@endPos smallint

    IF (RIGHT(@String, 1) != @Delimiter)
        SET @String = @String + @Delimiter
    SET @startPos = 1
    SET @endPos = CharIndex(@Delimiter, @String)
    WHILE @endPos > 0
        BEGIN
            INSERT @Values(Value)
            SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
            -- remove the delimiter just used
            SET @String = STUFF(@String, @endPos, 1, '')
            -- move string pointer to next delimiter
            SET @startPos = @endPos
            SET @endPos = CHARINDEX(@Delimiter, @String)
    END
    RETURN
END

<!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin.  http://dunnhq.com -->This function can be used in sample stored procedure below.
<div style="display: inline; float: none; margin: 0px; padding: 0px;" class="wlWriterSmartContent"><!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->CREATE PROCEDURE GetCustomers     @customerIDs AS nvarcharASBEGIN    DECLARE @custIDs AS table (Value nvarchar(255) Not Null)    INSERT INTO @custIDs        SELECT Value FROM [fn_ParseDelimitedStrings](@customerIDs, ',')     SELECT * FROM Orders WHERE CustomerID IN (SELECT Value FROM @custIDs)END