使用用户定义表类型作为存储过程参数时遇变量未声明错误求助

刚接触用户定义表类型，尝试修改存储过程使其接收该类型参数。现有存储过程getUser负责向表类型变量插入数据，代码如下：

CREATE PROCEDURE [dbo].[getUser]
    (@userID int = NULL)
AS
BEGIN
    DECLARE @PIDTable AS posts_id_table_type;

    INSERT INTO @PIDTable
        SELECT DISTINCT post_id 
        FROM siteTable.posts
        WHERE poster_id LIKE @userID

随后修改存储过程getPostThread使其接收该表类型参数，代码如下：

ALTER PROCEDURE [dbo].[getPostThread]
    @PIDTable posts_id_table_type READONLY
AS
BEGIN
    SELECT
        poster_id, post_id, post_time, post_text, 
        REPLACE(REPLACE(REPLACE(post_text, ',', ';'), CHAR(13), '|'), CHAR(10), '|') AS post_text_amended
    FROM 
        siteTable.posts
    WHERE 
        post_id IN (@PIDTable)
    ORDER BY 
        post_id ASC, post_time
END

执行ALTER语句时出现错误：

Must declare the scalar variable "@post_id_table"

疑惑是否因未从getUser直接调用getPostThread导致该错误，寻求问题原因及解决办法。

错误原因 #

错误和是否调用getUser无关，核心问题是在WHERE子句中错误地将表类型变量当作标量变量使用。@PIDTable是用户定义表类型，本质是一张表，不能直接放入IN()中——IN()需要的是标量值列表，而非表对象。错误提示里的@post_id_table是SQL Server解析时的误报，实际根源在IN (@PIDTable)这行的语法错误。

解决步骤 #

1. 修正getPostThread的查询逻辑 #

把表类型变量当作表来查询，用子查询或JOIN替代原写法：

子查询写法

ALTER PROCEDURE [dbo].[getPostThread]
    @PIDTable posts_id_table_type READONLY
AS
BEGIN
    SELECT
        poster_id, post_id, post_time, post_text, 
        REPLACE(REPLACE(REPLACE(post_text, ',', ';'), CHAR(13), '|'), CHAR(10), '|') AS post_text_amended
    FROM 
        siteTable.posts
    WHERE 
        post_id IN (SELECT post_id FROM @PIDTable)
    ORDER BY 
        post_id ASC, post_time
END

JOIN写法（性能更优）

ALTER PROCEDURE [dbo].[getPostThread]
    @PIDTable posts_id_table_type READONLY
AS
BEGIN
    SELECT
        p.poster_id, p.post_id, p.post_time, p.post_text, 
        REPLACE(REPLACE(REPLACE(p.post_text, ',', ';'), CHAR(13), '|'), CHAR(10), '|') AS post_text_amended
    FROM 
        siteTable.posts p
    INNER JOIN @PIDTable pt ON p.post_id = pt.post_id
    ORDER BY 
        p.post_id ASC, p.post_time
END

2. 补充getUser调用getPostThread的逻辑（可选） #

如果需要从getUser直接调用getPostThread，可在getUser中添加调用语句：

CREATE PROCEDURE [dbo].[getUser]
    (@userID int = NULL)
AS
BEGIN
    DECLARE @PIDTable AS posts_id_table_type;

    INSERT INTO @PIDTable
        SELECT DISTINCT post_id 
        FROM siteTable.posts
        WHERE poster_id = @userID; -- 建议用=替代LIKE，除非需要模糊匹配

    -- 传入表类型参数调用存储过程
    EXEC dbo.getPostThread @PIDTable = @PIDTable;
END

内容的提问来源于stack exchange，提问作者Buzzkillionair