如何将 udtt 传递到 SQL Server Management Studio 中的存储过程 [英] How to pass udtt into a stored procedure in SQL Server Management Studio
问题描述
我有一个具有以下签名的 SP prc_Foo_Delete:
I have a SP prc_Foo_Delete which has the following signature:
ALTER PROCEDURE [prc_Foo_Delete]
@fooIds [int_udtt] READONLY,
@deleteReason int,
@comment nvarchar(512),
@deletedBy nvarchar(128)
int_udtt 定义为:
int_udtt is define as:
CREATE TYPE [int_udtt] AS TABLE(
[Id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
我尝试使用以下脚本在 Management Studio 中调用此 SP:
I tried to call this SP in Management Studio with following script:
DECLARE @return_value int
EXEC @return_value = [prc_Foo_Delete]
@fooIds = 3,
@deleteReason = 2,
@comment = N'asfdasdf',
@deletedBy = N'asdfa'
SELECT 'Return Value' = @return_value
GO
我得到的错误是:操作数类型冲突:int 与 int_udtt 不兼容.如何传入 int 或 int 列表以在此工具中调用(我知道如何在代码中执行此操作,但在 Management Studio 中不知道).
The error I got is: Operand type clash: int is incompatible with int_udtt. How do I pass in a int or a list of int to call in this tool (I know how to do it in code but not in Management Studio).
推荐答案
由于您已将用户定义类型定义为存储过程的参数,因此您需要使用该用户定义类型调用存储过程的时候也一样!你不能只发送一个 INT
而不是......
Since you've defined your user defined type as a parameter on the stored procedure, you need to use that user-defined type, too, when calling the stored procedure! You cannot just send in a single INT
instead....
尝试这样的事情:
-- define an instance of your user-defined table type
DECLARE @IDs [int_udtt]
-- fill some values into that table
INSERT INTO @IDs VALUES(3), (5), (17), (42)
-- call your stored proc
DECLARE @return_value int
EXEC @return_value = [prc_Foo_Delete]
@fooIds = @IDs, -- pass in that UDT table type here!
@deleteReason = 2,
@comment = N'asfdasdf',
@deletedBy = N'asdfa'
SELECT 'Return Value' = @return_value
GO
这篇关于如何将 udtt 传递到 SQL Server Management Studio 中的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!