How to pass output parameter to a Stored Procedure?

Question

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 

I get an error message saying:

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'

What should I do?

Thanks in advance.

Answer 1

The error message is self-explanatory - you should name all of 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;