T-SQL:类似于C#参数的概念

在SQL 2008中,有表值参数(TVP)

最后,我们可以在不依赖XML的情况下执行IN子句!

不,不适用于UDF或存储过程等。这就是桌子的用途。将值放在表中的某个位置(使用公共键),并将正确的键传递给您的过程。

CREATE PROCEDURE dbo.sptest 
( @xml TEXT )
AS 
BEGIN
DECLARE @flag1 INT
DECLARE @flag2 VARCHAR(50)
DECLARE @flag3 DATETIME

DECLARE @idoc INT
exec sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT @flag1 = firstparam, flag2 = secondparam, flag3 = thirdparam
FROM OPENXML(@idoc, '/root', 2) WITH
( firstparam INT, secondparam VARCHAR(50), thirdparam DATETIME) as x

END


exec sptest '<root><firstparam>5</firstparam><secondparam>Joes Bar</secondparam><thirdparam>12/30/2010</thirdparam></root>'

根据需要进行扩展

我见过的传递参数或数组的另一种方法是传递XML字符串,将其转储到临时表/表变量中,然后从那时开始使用它。当您想手动运行存储过程时,这不是最简单的,但它可以解决缺乏数组/动态参数支持的问题。

我使用了一个小函数将CSV字符串分隔成一个表

这样我就可以走了

SELECT col1, col2
FROM myTable
WHERE myTable.ID IN (SELECT ID FROM dbo.SplitIDs('1,2,3,4,5...'))

CREATE FUNCTION [dbo].[SplitIDs]
(
    @IDList varchar(500)
)
RETURNS 
@ParsedList table
(
    ID int
)
AS
BEGIN
    DECLARE @ID varchar(10), @Pos int

    SET @IDList = LTRIM(RTRIM(@IDList))+ ','
    SET @Pos = CHARINDEX(',', @IDList, 1)

    IF REPLACE(@IDList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @ID = LTRIM(RTRIM(LEFT(@IDList, @Pos - 1)))
            IF @ID <> ''
            BEGIN
                INSERT INTO @ParsedList (ID) 
                VALUES (CAST(@ID AS int)) --Use Appropriate conversion
            END
            SET @IDList = RIGHT(@IDList, LEN(@IDList) - @Pos)
            SET @Pos = CHARINDEX(',', @IDList, 1)

        END
    END 
    RETURN
END