Sql Server: How to execute a stored procedure with OUTPUT parameters and RETURN variables.
- Posted by Sqltimes
- On March 1, 2014
- 0 Comments
Quick one today:
Sometimes we all need to write/execute stored procedures manually in SSMS. When stored procedures have OUTPUT parameters, the execution is slightly different (counter intuitive). So here is a sample on how to execute a stored procedure with OUTPUT parameters and RETURN statement.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
CREATE PROCEDURE dbo.usp_Sample_For_Output_Return @Param1 INT , @Param2 INT , @Output_Param1 INT OUTPUT , @Output_Param2 VARCHAR(20) OUTPUTASBEGIN -- -- do something with input parameters -- -- some code -- -- Load values into output parameters -- SELECT @Output_Param1 = D.database_id , @Output_Param2 = D.name FROM sys.databases AS D WHERE D.name = 'master' -- -- sample use of RETURN statement -- IF @@ERROR <> 0 RETURN 1 ELSE RETURN 0ENDGO |
As you can see, this above procedure has a few input parameters and a couple of OUTPUT parameters. Now, let’s see how to execute this procedure while capturing the value from the OUTPUT parameters into a variable for usage after executing the procedure.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE @Input1 INT = 1 , @Input2 INT = 2 , @Output1 INT , @Output2 VARCHAR(20) , @ret INTEXECUTE @ret = usp_Sample_For_Output_Return @Param1 = @Input1 , @Param2 = @Input2 , @Output_Param1 = @Output1 OUTPUT , @Output_Param2 = @Output2 OUTPUTSELECT @ret AS Return_Variable , @Output1 AS [Database_ID] , @Output2 AS [Database_Name]GO |
Hope this helps,
_Sqltimes
0 Comments