Returning multiple values from a stored proc.

[sso]

I have a stored procedure (show below) that I need to return a few of
the fields. Its just a basic login, nothing high security. I can't
figure out how to get it to return the fields I require. Any
assistance? (I'm using C# and SQL Express 2008).

Kind regards.
Thanks!






ALTER PROCEDURE [dbo].[auth]
-- Add the parameters for the stored procedure here
(
@user varchar output,
@password varchar output
)
AS
DECLARE
@id int,
@pos varchar


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT PersonID, Rank, Position, Supervisor FROM PERSON WHERE
UserName = @user AND Password = @password
END

[Raghupathi Kamuni]

       > @user varchar output,
       > @password varchar output

 

      Why do you need output parameters here ?

[Muhammad Arif]

what i am thinking that.. out parameter with user result that .. user can be checked on each page or forms for permission pupose..

2. out parameter with password may be used when the session time out occur so the user have to again give user name and password.. so i will not connect with db again but will check with those which is already get first..

--
Senior Software Engineer

[JH Lee]

My recommendation will be just to use a set of record or table for
output.

On Nov 26, 5:26 pm, Muhammad Arif <brainsa...@gmail.com> wrote:
> what i am thinking that.. out parameter with user result that .. user can be
> checked on each page or forms for permission pupose..
>
> 2. out parameter with password may be used when the session time out occur
> so the user have to again give user name and password.. so i will not
> connect with db again but will check with those which is already get first..
>
> On Thu, Nov 26, 2009 at 11:21 AM, Raghupathi Kamuni

> <raghukam...@gmail.com>wrote:

>
>
>
> >        > @user varchar output,
> >        > @password varchar output
>
> >       Why do you need output parameters here ?
>

[Cerebrus]

That doesn't make any sense at all ! The username and password would
be passed from the UI and it seems very weird for them to be used as
OUTPUT parameters. Further, the @id and @pos variables seem to be
lying unused.

On Nov 26, 11:26 am, Muhammad Arif <brainsa...@gmail.com> wrote:
> what i am thinking that.. out parameter with user result that .. user can be
> checked on each page or forms for permission pupose..
>
> 2. out parameter with password may be used when the session time out occur
> so the user have to again give user name and password.. so i will not
> connect with db again but will check with those which is already get first..
>
> On Thu, Nov 26, 2009 at 11:21 AM, Raghupathi Kamuni

> <raghukam...@gmail.com>wrote:

[Brynn V1.0.0]

/*
Hi there.
*/

ALTER PROCEDURE [dbo].[auth]
-- Add the parameters for the stored procedure here

-- *** "output"s removed ***
-- *** varchar --> varchar(100) to finish their declaration ***
-- *** only assumed 100 ... match your table parameters ***
(
@user varchar(100),
@password varchar(100)
)

AS

DECLARE
-- *** @id int, @pos varchar *** Not needed or being used ***

BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here

-- **

SELECT PersonID, Rank, Position, Supervisor FROM PERSON

WHERE UserName = @user AND Password = @password;

SET NOCOUNT OFF; -- *** switch it back before end ***
END



/* -----
If it were me, I would use the procedure above.

Call it into a SqlDataReader.
Place a counter=0 just outside the loop you will use
to loop through the 1 record. Also, counter += 1 in
that loop.

if (counter == 1) // login successful
{
//successful login code
}
else //counter still 0, no record returned
{
//login unsuccessful code
}
-----
*/

[Brynn V1.0.0]

ALTER PROCEDURE [dbo].[auth]
-- Add the parameters for the stored procedure here
(

-- removed "output" from below ... your c# page
-- should still have these values when you get back
-- just guessed on 100 dimension

@user varchar(100),
@password varchar(100)
)

AS

-- The following variables are not being used, nor will they need to
be.
-- DECLARE @id int, @pos varchar

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT PersonID, Rank, Position, Supervisor FROM PERSON

WHERE UserName = @user AND Password = @password;

--SELECT 'testing';

SET NOCOUNT OFF; -- I recommend turning back off, but up to your
designs.
END






And without sharing my sqlArray library at this time (which uses
datareader to
place the data into a jagged array ... because I used to love getRows,
and like
closing that db connection asap) ... I leave you with the following
link
for using SqlDataReader. I will have a personal site someday I hope.

http://articles.sitepoint.com/article/dataset-datareader

Throw a counter in the datareader loop start it above the loop at 0
and then make it =1 in the loop ... if 0 the login failed.
If your counter is 1 then login successful.

if (myCount == 1) //success
{
// set the sessionID["user"] or cookie to PersonID
// not sure what you are using rank, position, and supervisor for in
app
}
else // login failed
{
// redirect to failed login page
}