How to pass an output parameter to a stored procedure?

Question

I wrote a stored procedure in 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 execute the procedure:

 DECLARE @finaloutput1 varchar(300) EXEC usp_data_migration 'Yousuf', 'emp', '[City Branch]', 'emp_tgt', @finaloutput1 output SELECT @finaloutput1 

Following this path, I am not making the right conclusion.

When I execute this path:

 DECLARE @finaloutput1 varchar(300) EXEC usp_data_migration @sourceDatabase = 'Yousuf', @sourceTable = 'emp', @targetDatabase = '[City Branch]', @targetTable = 'emp_tgt', @finaloutput1 output SELECT @finaloutput1 

I get an error message:

Msg 119, Level 15, State 1, Line 41
You must pass parameter number 5 and the following parameters as "@name = value". After using the form "@name = value", all subsequent parameters must be passed in the form "@name = value".

And if I delete my output parameter and execute the procedure, I get the desired result, but I can not get the result as output.

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

What should I do?

Thanks in advance.

Answer 1

You need to choose like this

Example 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 

Example 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 

See here for more details.

Answer 2

The error message is self-evident - you must specify all your parameters.

 DECLARE @finaloutput1 varchar(300); EXEC dbo.usp_data_migration -- always use schema prefix @sourceDatabase = 'Yousuf', @sourceTable = 'emp', @targetDatabase = '[City Branch]', @targetTable = 'emp_tgt', @finaloutput = @finaloutput1 OUTPUT; SELECT @finaloutput1; 

Answer 3

A simple example:

 create procedure proc2 @var int out,@var2 varchar(10) out as begin set @var=(select max(id) from customer); set @var2=(select name from customer where id=@var ); end declare @maxid int; declare @maxname varchar(10); exec proc2 @maxid out,@maxname out; select @maxid,@maxname;