我可以在存储过程中有一个可选的 OUTPUT 参数吗?
我有一个存储过程，它有一堆输入和输出参数，因为它正在向多个表插入值.在某些情况下，存储过程仅插入到单个表中(取决于输入参数).这是一个模拟场景来说明.
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
相关文章