传多个值放到存储过程的一个变量中的处理方法
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
页:
[1]