带输出参数的SQL Server存储过程执行疑问及规范咨询

我基于AdventureWorks2019数据库开发练习用ERP程序时，遇到存储过程sohead的执行问题：只有将所有输出参数指定为NULL时才能正常执行，其他类似存储过程无此情况。咨询以下问题：

1. 该问题产生的原因是什么？
2. 其他类似存储过程是否存在故障？
3. 带输出参数的存储过程的标准语法执行方式是什么？
4. 相关执行技巧有哪些？

存储过程代码及执行语句 #

存储过程定义 #

drop procedure if exists sohead; 

create procedure sohead
    @BusinessEntityid int,                      
    @customerid int,                         
    @persontype nchar(2) output,        
    @storeid int output,                    
    @orderdate datetime output,              
    @duedate datetime output,               
    @accountnumber nvarchar(15) output,     
    @salespersonid int output,              
    @territoryid int output,            
    @billToAddressid int output,    
    @shipToAddressid int output,    
    @shipMethodid int,  
    @creditcardid int output,   
    @currencyrateid int =null,     
    @subtotal money, 
    @taxamt money,    
    @freight money,     
    --totaldue self generated and computed
    @comment nvarchar(128)  
AS
BEGIN 
    set nocount on;
        begin try
        
            begin transaction
            --@storeid
            
                select @storeid = storeid from sales.customer where CustomerID = @customerid;

            --orderdate 
            SET @orderdate = GETDATE();
            --duedate
            SET @duedate = DATEADD(day, 15, @orderdate);

            --salespersonid
            
            if @storeid IS NULL
                SET @salespersonid = NULL;
            else
                SELECT @salespersonid = SalesPersonID 
                FROM Sales.Store 
                WHERE BusinessEntityID = @storeid;

            --territoryid
            select @territoryid = territoryid from sales.Customer
            where customerid = @customerid;

            --biiltoaddress  AND  shipaddressid
            select @persontype = persontype FROM person.Person
                    WHERE BusinessEntityID = @businessentityid;

            if @persontype = 'SC' 
            BEGIN
                        ;with billshipadd as(
                            SELECT AddressID FROM  
                            person.BusinessEntityAddress

                            where BusinessEntityid = @storeid
    
                        )
                        select   @BILLTOADDRESSID = addressid,@SHIPTOADDRESSID = addressid  from billshipadd; --address GIA 'SC'
            
            END
            ELSE IF @PERSONTYPE = 'IN' 
            BEGIN
                        ;with billshipadd as(
                            SELECT AddressID FROM  
                            person.BusinessEntityAddress

                            where BusinessEntityid = @businessentityid
    
                        )
                        select   @BILLTOADDRESSID = addressid,@SHIPTOADDRESSID = addressid  from billshipadd; --address gia 'IN'
            END;             

            --creditcardid

            select @creditcardid =  CreditCardID from Sales.PersonCreditCard where businessentityid = @BusinessEntityid;

            --accountnumber 

            select @accountnumber = accountnumber from sales.customer where customerid = @customerid;

            --currencyrateid

            --null


            insert into sales.salesorderheader(revisionNumber, orderdate, duedate, shipdate, status,
            onlineorderflag, purchaseordernumber, accountnumber, customerid, salespersonid, territoryid,        
    billtoaddressid, shiptoaddressid, shipmethodid, creditcardid, creditcardapprovalcode,       
    currencyrateid, subtotal, taxamt, freight, comment, rowguid, modifieddate)
            VALUES(
            0,
            @orderdate,
            @duedate,
            null,
            1,
            0,
            null,
            @accountnumber,
            @customerid,
            @salespersonid,
            @territoryid,
            @billToAddressid,
            @shipToAddressid,
            @shipMethodid,
            @creditcardid,
            null, --credit card approval code
            @currencyrateid,
            @subtotal,
            @taxamt,
            @freight,
            --@totaldue, --mono toy
            @comment,
            newid(),
            getdate()
    );

            commit transaction
        end try
        begin catch 
            SELECT
                ERROR_NUMBER() AS ErrorNumber,
                ERROR_SEVERITY() AS ErrorSeverity,
                ERROR_STATE() AS ErrorState,
                ERROR_MESSAGE() AS ErrorMessage;

        IF @@TRANCOUNT > 0
            rollback transaction;
            throw;
        end catch
end;

正确执行语句 #

exec sohead @businessentityid=1983 , @customerid=30113 ,@shipmethodid= 1, @subtotal =5,
@taxamt =5, @freight = 5, @comment = null,@persontype = null, @storeid = null,@orderdate = null, @duedate = null, @accountnumber= null, @salespersonid= null,
@territoryid = null, @billToAddressid = null, @shipToAddressid=null, @creditcardid = null;

报错的执行语句 #

exec sohead @businessentityid=1983 , @customerid=30113 ,@shipmethodid= 1, @subtotal =5,
@taxamt =5, @freight = 5;

问题解答 #

1. 问题产生的原因 #

核心有两点：

• 输出参数无默认值且未显式指定：存储过程中所有OUTPUT参数（如@persontype、@storeid等）都没有设置默认值，SQL Server要求这类参数执行时必须显式声明——要么用变量接收输出结果，要么明确赋值为NULL（表示不需要接收输出）。
• 遗漏必填输入参数：@comment是无默认值的输入参数，执行时必须传入值（哪怕是NULL），报错的执行语句中完全遗漏了该参数，这也是导致执行失败的直接原因之一。

2. 其他类似存储过程是否存在故障 #

不会存在故障，原因是其他类似存储过程要么满足以下条件之一：

• 输出参数设置了默认值（如@persontype nchar(2) = NULL OUTPUT），执行时可省略该参数；
• 执行时正确处理了输出参数（用变量接收或显式传NULL）；
• 参数定义逻辑不同（比如部分参数为输入型而非输出型，或有合理的默认值配置）。

3. 带输出参数的存储过程的标准语法执行方式 #

分两种场景：

场景1：需要接收输出参数的值

先声明对应类型的变量，执行时用OUTPUT关键字标记参数，示例：

-- 声明接收输出的变量
DECLARE @persontype nchar(2), @storeid int, @orderdate datetime

-- 执行存储过程，用变量接收输出
exec sohead 
    @businessentityid=1983, 
    @customerid=30113, 
    @shipmethodid=1, 
    @subtotal=5, 
    @taxamt=5, 
    @freight=5, 
    @comment=null,
    @persontype=@persontype OUTPUT,
    @storeid=@storeid OUTPUT,
    @orderdate=@orderdate OUTPUT,
    -- 其他输出参数同理
    @duedate=null, 
    @accountnumber=null, 
    @salespersonid=null,
    @territoryid=null, 
    @billToAddressid=null, 
    @shipToAddressid=null,
    @creditcardid=null;

-- 查看输出结果
SELECT @persontype AS PersonType, @storeid AS StoreID, @orderdate AS OrderDate;

场景2：不需要接收输出参数的值

直接给输出参数显式赋值为NULL，就像你提供的正确执行语句那样，确保所有无默认值的参数都被指定。

4. 相关执行技巧 #

• 给输出参数设置默认值：定义存储过程时，给输出参数加上默认值（如@persontype nchar(2) = NULL OUTPUT），这样执行时不需要接收输出的参数可以直接省略，简化执行语句。
• 使用命名参数传参：执行时始终用@参数名=值的格式传参，避免依赖参数顺序，减少遗漏或传参错误。
• 区分输入输出参数：需要接收输出时，必须用OUTPUT关键字标记对应的变量，否则无法获取输出值。
• 调试输出参数：在存储过程中可加入SELECT @persontype, @storeid这类语句，快速验证输出参数的赋值逻辑是否正确。
• 必填参数必传：所有无默认值的输入参数（如@comment），执行时必须传入值，哪怕是NULL。

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