存储过程中可以有一个可选的OUTPUT参数吗? [英] Can I have an optional OUTPUT parameter in a stored procedure?
问题描述
我有一个存储过程,该存储过程具有一堆输入和输出参数,因为它正在向多个表中插入值。在某些情况下,存储的proc仅插入到一个表中(取决于输入参数)。下面是一个模拟的场景来说明。
I have a stored procedure that has a bunch of input and output parameters because it is Inserting values to multiple tables. In some cases the stored proc only inserts to a single table (depending on the input parameters). Here is a mocked up scenario to illustrate.
表/数据对象:
人
Id
Name
Address
名称
Id
FirstName
LastName
地址
Id
Country
City
说我有一个插入人的存储过程。如果该地址不存在,则不会将其添加到数据库的 Address
表中。
Say I have a stored procedure that inserts a person. If the address doesn't exist I won't add it to the Address
table in the database.
因此,当我生成代码来调用存储过程时,我不想打扰添加 Address
参数。对于 INPUT
参数,这是可以的,因为SQL Server允许我提供默认值。但是对于 OUTPUT
参数,我应该在存储过程中做什么使其使其成为可选的,这样我就不会收到错误...
Thus when I generate the code to call the stored procedure I don't want to bother adding the Address
parameter. For INPUT
parameters this is ok because SQL Server allows me to supply default values. But for the OUTPUT
parameter what do I do in the stored procedure to make it optional so I do not receive an error...
过程或函数'Person_InsertPerson'期望未提供参数
'@AddressId'。
推荐答案
输入和输出参数都可以分配默认值。在此示例中:
Both input and output parameters can be assigned defaults. In this example:
CREATE PROCEDURE MyTest
@Data1 int
,@Data2 int = 0
,@Data3 int = null output
AS
PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)
SET @Data3 = @Data3 + 1
RETURN 0
第一个参数是必需的,第二个和第三个是可选的-如果调用例程未设置,则将为其分配默认值。尝试弄乱它,并使用不同的值和设置在SSMS中使用以下测试调用例程,以查看它们如何协同工作。
the first paramter is required, and the second and third are optional--if not set by the calling routine, they will be assigned the default values. Try messing around with it and the following test-call routine in SSMS using different values and settings to see how it all works together.
DECLARE @Output int
SET @Output = 3
EXECUTE MyTest
@Data1 = 1
,@Data2 = 2
,@Data3 = @Output output
PRINT '---------'
PRINT @Output
这篇关于存储过程中可以有一个可选的OUTPUT参数吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!