如何将输出参数传递给存储过程? [英] How to pass output parameter to a Stored Procedure?

问题描述

我用以下格式编写了一个存储过程:

I have written a stored procedure with the following format:

ALTER PROCEDURE usp_data_migration 
   (@sourceDatabase varchar(50),
    @sourceTable varchar(50),
    @targetDatabase varchar(50),
    @targetTable varchar(50),
    @finaloutput varchar(max) output)
AS
BEGIN
----Set of SQL Blocks


END

然后，我正在执行该过程:

Then, I am executing the procedure:

DECLARE @finaloutput1 varchar(300)

EXEC usp_data_migration 'Yousuf', 'emp', '[City Branch]', 'emp_tgt', @finaloutput1 output 

SELECT @finaloutput1 

通过这种方式执行，我无法正确输出.

By executing this way I don't proper output.

当我以这种方式执行时:

When I execute this way:

DECLARE @finaloutput1 varchar(300)

EXEC usp_data_migration @sourceDatabase = 'Yousuf',
                        @sourceTable = 'emp',
                        @targetDatabase = '[City Branch]',
                        @targetTable = 'emp_tgt',
                        @finaloutput1 output 

SELECT @finaloutput1 

我收到一条错误消息:

第119条消息，第15级，状态1，第41行
必须传递参数编号5和后续参数作为"@name = value".使用'@name = value'形式后，所有后续参数都必须以'@name = value'形式传递.

Msg 119, Level 15, State 1, Line 41
Must pass parameter number 5 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

如果我删除了输出参数并执行了该过程，则会得到所需的输出，但无法将结果作为输出.

And if I removed my output parameter and execute the procedure, I get my desired output but I am not able to get my result as an output.

EXEC usp_data_migration @sourceDatabase = 'Yousuf',
                        @sourceTable = 'emp',
                        @targetDatabase = '[City Branch]',
                        @targetTable = 'emp_tgt'

我该怎么办?

谢谢.

推荐答案

您必须选择这样

示例1

  create procedure p1
    (
    @id INT,
    @name varchar(20) OUTPUT,
    @company varchar(20) OUTPUT
    )
    AS
     BEGIN
    Set @name = 'name'
    Set @company = 'company'
        select @name , @company from table1 where id = @id;
     END
    GO

示例2

CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

请在此处参阅更多信息

这篇关于如何将输出参数传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！