Tuesday, February 7, 2012

Return values from a sql server stored procedure

If you want to return a value from a procedure ,the parameter which return the value has to be declared using the output class
note:A procedure can return one or n values at a time
procedure test(@x int,@y int output)
                |         |
              input     output
              Parameter  Parameter
Create procedure process(@x int,@y int,@z int output)
As
Begin
SET @z:@x+@y
END
//calling the above procedure
Declare @a int
EXEC Process 100,50,@A output
print @A
Note:
While returning a value from a procedure you don't require to use any return statement .If you assign the values to the output parameter ,procedure only will take the responsibility of returning the values

Procedure with multiple output parameters:
Here i will show a procedure which can be used for inserting the id,name,sales and city into orders table
Create procedure insert_orders(@id int,@name varchar(50),@sales money,@city varchar(50) 
AS
BEGIN
Insert into orders(Id,name,sales,city)values(@id ,@name,@sales,@city)
END
*calling above procedure
EXEC insert_orders 1,'aaa',3000,US

Post a Comment

Bel