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

最新推荐文章于 2023-01-05 15:16:54 发布

原创最新推荐文章于 2023-01-05 15:16:54 发布 · 445 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Windows

本文介绍了一个SQL函数fn_ParseDelimitedStrings，用于将一个由指定分隔符分隔的字符串拆分为多个记录，并提供了一个使用该函数从逗号分隔的客户ID中获取客户订单的示例存储过程。

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

This function can be used in sample stored procedure below.

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE PROCEDURE GetCustomers 
    @customerIDs AS nvarchar
AS
BEGIN
    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