Returning values from Stored Procedure

Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?

Thanks,It depends on whether you want a *return* value or an *output* value. You can return only one value, but many output params.

Example of return value:

CREATE PROCEDURE myProc1
@.Param INT
AS
DECLARE @.Return INT
SELECT @.Return=SomeColumn FROM MyTable
 WHERE SomeKey=@.Param
RETURN @.Return
GO

Example of output parameters:

CREATE PROCEDURE myProc2
@.Param INT,
@.Output1 NVARCHAR(20) OUTPUT,
@.Output2 NVARCHAR(50) OUTPUT
AS
SELECT @.Outpu1=SomeColumn, @.Output2=AnotherColumn FROM MyTable
 WHERE SomeKey=@.Param
GO

HTH!