时间戳

首页 > 解决方案 > 以表变量为参数执行存储过程

sql - 以表变量为参数执行存储过程

问题描述

有谁知道为什么这个查询不起作用？如何将表变量 itemId 作为参数添加到 exec 语句？谢谢

DECLARE @test TABLE 
(
itemId UNIQUEIDENTIFIER,
finalAmount DECIMAL
);
     
INSERT INTO @test EXEC [GetItems] 

DECLARE @sql NVARCHAR(max)

DECLARE @param NVARCHAR(max)
SET @param = N'select itemId from @test'

SELECT @sql = 'EXEC [InsertTestItem]'+' ' + @param;
SELECT @sql
EXEC(@sql)

标签： sqlsql-server

解决方案

请参阅 SQL Server 中的完整工作示例，您应该能够一个接一个地运行每个块，以查看它从您传递到存储过程的表类型中选择了所有内容

-- Create the Table type that we will use in the stored proc------------------------

IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MyIdTableType')
BEGIN
    PRINT 'Creating type [dbo].[MyIdTableType]'
    CREATE TYPE [dbo].MyIdTableType AS TABLE (
        Id BIGINT
    )
END 
GO

-- Create a stored proc that uses it ------------------------

CREATE PROCEDURE [dbo].[UsMyTabelType]
    @IdsTable AS [dbo].MyIdTableType Readonly
AS

BEGIN    
    -- Now you have the data you can use it like any normal table and join on it
    SELECT * FROM @IdsTable
END
GO

-- Lets test it out ------------------------

DECLARE @myIds AS MyIdTableType

INSERT INTO @myIds (Id) 
VALUES 
(1),
(2), 
(3)

EXEC dbo.UsMyTabelType @IdsTable = @myIds

推荐阅读