T-SQL:与C#params类似的概念

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

存储的进程可以接受参数列表。。

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

迈克

典型的

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>'

我使用了一个小函数将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

我确信有更好的方法来实现这一点,这是我在网上找到的一种方法,它对我正在做的事情很有效。如果有一些改进可以做,请评论。