How To Return Multiple Parameters from a Stored Procedure

Experts,

I have a stored proc that I am passing 8 parameters too, 3 of which I want to return:

Beginning of stored proc looks like this:

ALTER PROCEDURE [dbo].[sp_CheckUserLogin]
      -- Parameters:
      @Email varchar(100),
      @Password varchar(50),
      @LoginDate smalldatetime,
      @Browser varchar(20),
      @IpAddress varchar(20),
      @UserID numeric(18,0) OUTPUT,
      @UserName varchar(100) OUTPUT,
      @FailedAttemptCount smallint OUTPUT
AS
DECLARE
      -- Local Variables:
      @RecordCount int = 0,
      @LastAttempt smalldatetime
BEGIN


I know to return one parameter I would do this at the end:

RETURN @UserID

But how to I return all 3?  I've been looking online all over, and all the examples only show returning one value.
What is the proper syntax to return all 3 of my output parameters?
LVL 10
P1ST0LPETEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

soujanya_gCommented:
Hi

You return all the paramenters as table data through select statement at end of the stored procedure

Like Select Param1,param2,Param3  

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this demo script
create procedure tst_proc @input_value int, @output_value varchar(10) output
as
begin
  set @output_value = 2*@input_value
  return 99
end 
go
declare @i int
declare @o varchar(10)
declare @r int

exec @r = tst_proc 10, @r output
select @i, @o, @r

go
drop procedure tst_proc

Open in new window

arilaniCommented:
You can assign the values to these parameters and the y will be returned to the caller
ALTER PROCEDURE [dbo].[sp_CheckUserLogin] 
      -- Parameters:
      @Email varchar(100),
      @Password varchar(50),
      @LoginDate smalldatetime,
      @Browser varchar(20),
      @IpAddress varchar(20),
      @UserID numeric(18,0) OUTPUT,
      @UserName varchar(100) OUTPUT,
      @FailedAttemptCount smallint OUTPUT
AS
DECLARE
      -- Local Variables:
      @RecordCount int = 0,
      @LastAttempt smalldatetime
BEGIN
...
SELECT @UserID = ..., @UserName = ..., @FailedAttemptCount = ......
END

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
P1ST0LPETEAuthor Commented:
thanks for the help all.
P1ST0LPETEAuthor Commented:
Good answers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.