How to pass output parameter to a Stored Procedure?
Solution 1
You have to Select 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
Solution 2
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;
Yousuf Sultan
• Design, develop and deliver BI solutions such as reports, dashboards and, ad hoc data access solutions with consideration to best practices and reporting needs. • Understand the data model as well as become familiar with the business information in each of the source systems. • Build effective reporting dashboards. • Ability to create BI visualizations using Tableau that provides insights for our Business customers. • Extensive knowledge in various reporting objects like Calculated Fields, Groups, Sets, Hierarchies, Parameters, Filters, Actions, Formatting and sorting in Tableau. • Worked extensively on joining tables, multiple data connections using Data Blending. • Analyse requirements and develop ETL/BI with minimal supervision. • Work with analysts for technical delivery of reporting solutions. • Debug report issues by analysing the data sources and SQLs to provide quick resolution. • Extensive experience in reporting and Business Intelligence applications • Excellent data analysis and SQL skills • Ability to handle multiple tasks simultaneously, react to problems quickly and understand the complexity. • Technical documentation of all delivered artifacts. • Excellent attention to detail and organizational skills. • Mentor team members on various aspects of BI development. • Ability to work in fast-paced environment and meet tight deadlines. • Proficient in writing T-SQL queries for reporting and data analysis. • 6+ years hands-on experience with SQL/T-SQL and SSIS/SSRS • 3+ years' experience with Tableau • 1+ years’ experience with Power BI.
Updated on July 05, 2022Comments
-
Yousuf Sultan almost 2 years
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.