如何将表传递给存储过程?

I am making my first attempt at creating a stored procedure (using VS2012 to edit) I am trying to pass my stored procedure a table with two columns. However when I click on parse to check my code I get the following errors.

我正在尝试创建一个存储过程(使用VS2012编辑)，我正在尝试将一个包含两列的表传递给存储过程。但是，当我单击parse以检查代码时，我得到了以下错误。

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

Msg 156，第15级，状态1，第1行不正确的语法在关键字“AS”附近。

Msg 102, Level 15, State 1, Procedure SaveMsgData, Line 10
Incorrect syntax near 'READONLY'.

Msg 102，第15级，状态1，过程SaveMsgData，第10行“READONLY”语法错误。

Msg 1087, Level 15, State 2, Procedure SaveMsgData, Line 45
Must declare the table variable "@tagList".

Msg 1087，第15级，状态2，程序SaveMsgData，第45行必须声明表变量“@tagList”。

I think the last two errors stem from the first, but I have no idea what is causing the first. As far as I can tell my syntax is correct.

我认为最后两个错误源于第一个错误，但是我不知道是什么导致了第一个错误。据我所知，我的语法是正确的。

Here is my full code.

这是我的全部代码。

CREATE TYPE TagListType AS TABLE
(                     
       TagID varchar(100),                
       AntNum int
);
GO

CREATE PROCEDURE SaveMsgData 
    @Mac nvarchar(30),
    @tagList TagListType READONLY
AS
    DECLARE @ReaderID int
    DECLARE @AntList TABLE (AntennaID int, AntNum int)

    SELECT @ReaderID = -1

    SELECT @ReaderID = Id
    FROM dbo.Readers
    WHERE MAC = @Mac

    IF (@ReaderID >= 0)
    BEGIN
        INSERT INTO @AntList (AntennaID,Antnum,TargetNumOfTags) 
            SELECT id, AntennaNumber, numOfTags 
            FROM Antennae
            WHERE ID = @ReaderID

        DECLARE @count int
        DECLARE @len int                    

        SET @count = 0
        SET @len = 0

        WHILE @count <= @len
        BEGIN
            DECLARE @LocalAnt int
            DECLARE @LocalTarget int
            DECLARE @LocalAntID int
            DECLARE @LocalStatus int
            DECLARE @LocalTagsRead int

            SELECT @LocalAnt = Antnum, @LocalTarget = TargetNumOfTags, @LocalAntID = AntID 
            FROM #AntList 
            WHERE Antnum = @count

            SELECT @LocalTagsRead = COUNT(*) FROM @tagList WHERE AntNum = @count

            IF @LocalTagsRead = @LocalTarget
            BEGIN
                SET @LocalStatus = 0
            END
        ELSE
            BEGIN
                SET @LocalStatus = 1
            END

        INSERT INTO Readings 
        VALUES (@LocalStatus, @LocalTarget, @LocalTagsRead, CURRENT_TIMESTAMP, @LocalAntID, @ReaderID)

        --Will insert Tags in to tag table here later.
        SET @count = @count + 1
    END
END
GO

The intent here is to have have my C# desktop application use this stored procedure.

这里的目的是让我的c#桌面应用程序使用这个存储过程。

2 个解决方案

#1

Table-valued parameters are available as of SQL Server 2008 - judging from the error message, you're most likely not running against a 2008 instance....

表值参数可用的SQL Server 2008——从错误消息,你最有可能不是针对2008年运行实例....

Are you truly on a 2008 or newer server engine??

你真的在使用2008或更新的服务器引擎吗?

Find out by using SELECT @@VERSION against your server - what does it report back?

通过对您的服务器使用SELECT @ version来查找-它会返回什么?

#2

You can pass a CSV parameter and convert it into XML with this function:

您可以传递一个CSV参数，并通过以下函数将其转换为XML:

create function udf_CsvToXML(@Csv as varchar(4096),@Delim as varchar(15)=',')
returns xml
as
begin
    declare @xml as xml = CAST('<XML>'+('<X>'+REPLACE(@Csv,@Delim,'</X><X>')+'</X></XML>') AS XML)
    return @xml
end

Then, you can use the XML in a join or whatever else as if it was a table:

然后，您可以在连接中使用XML，或者将其用作表:

DECLARE @TitlesXML as XML = dbo.udf_CsvToXML(@Titles,',')
select distinct t
from p
join    (SELECT N.value('.[1]', 'varchar(25)') as value FROM @TitlesXML.nodes('/XML/X') as T(N)) tt
    on tt.value = p.t

#1