INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Stored Procedures

Return value of a Stored Procedure by dbtech
Posted: 7 Jul 01

Passing parameters from one stored procedure to another

Example 1
Popular!

/*Call procedure*/
create  procedure proc1
as
/*Declare the output parameter*/
declare @result int
exec proc2 @result output
print @result
go

/*Actual procedure*/
Create procedure proc2 @paramout int output
as
/*Do something*/
SET @paramout=1
go
exec proc1

------------------------------------------------
Example 2
This makes use of the RETURN statement

create procedure usp_generateid @Addrid int,@hubid int
as
/*Do something */
Declare @outaddr int
IF @addriD>@hubid
BEGIN
    SET @outaddr=999
END
ELSE
BEGIN
    SET @outaddr=888
END
RETURN @outaddr /*Return the value using RETURN */
GO
/*Call Procedure*/
DECLARE @hubid int,
        @addrrid int,
        @OutAddrId int
/*need to declare @OutAddrId this holds the result*/

/*Assign values */
SELECT @addrrid=11,@hubid=9
EXECUTE @OutAddrId = usp_generateid @addrrid,@HubId
PRINT @OutAddrId


Note when u use the example 2
1>u can return only one value of integer type
2)CANNOT RETURN NULL VALUES, ONLY INTEGER, error message will be returned
2>Multiple values cannot be return
3>Mainly used for returning status of stored procedures or error codes

Praveen

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close