result set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

result set

Author

Topic

jjpatte
Starting Member

3 Posts

Posted - 2002-08-08 : 14:00:46

Hi,

In a stored procedure, how would I reference a column from this query:

SELECT *
FROM Table1
WHERE usercode = @UserCode
AND [password] = @Password

SET @DateExpired = Table1.DateExpired

Thanks!

dsdeming

479 Posts

Posted - 2002-08-08 : 14:16:14

SELECT @DateExpired = DateExpired
FROM Table1

HTH

jjpatte
Starting Member

3 Posts

Posted - 2002-08-08 : 14:20:16

Thanks but I need to return all of the values from the table.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 14:26:16

jjpatte, you should provide your table ddl, some sample data, and you desired rowset. I think you've got some confused readers.

Jay White
{0}

jjpatte
Starting Member

3 Posts

Posted - 2002-08-08 : 14:38:36

Sorry, I'm a beginner.

Basically, I have a stored procedure that looks to see whether a UserCode, Password, and PasswordExpirationDate are valid before logging a user in. Table1 contains this information along with other info that I need to return to my application.

What I want to do is determine whether GetDate() is greater than PasswordExpirationDate (one of the values returned by SELECT *).

BEGIN
SELECT *
FROM Table1
WHERE usercode = @UserCode
AND [password] = @Password

SET @TotalRecords = @@rowcount

/* If no records exist, then username / password is incorrect */
IF @TotalRecords = 0
SET @Message = 'Incorrect username or password.'
/* Ensure password hasn't expired */
ELSE IF GetDate() > PasswordExpirationDate
SET @Message = 'Password expired.'

END

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 14:56:06

BEGIN
SELECT *
FROM Table1
WHERE usercode = @UserCode
AND [password] = @Password

SET @TotalRecords = @@rowcount

/* If no records exist, then username / password is incorrect */
IF @TotalRecords = 0
SET @Message = 'Incorrect username or password.'
/* Ensure password hasn't expired */
ELSE IF GetDate() > PasswordExpirationDate
SET @Message = 'Password expired.'

select
@message = 'Password expired.'
from
dbo.Table1 u
where
u.usercode = @usercode and
u.password = @password and
getdate() > PasswordExpiredDate

END

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 15:11:14

Or, if you really wanted to be slick about it...


create proc usp_jjpatte
    @usercode varchar(10),
    @password varchar(10),
    @message varchar(20) out,
    @somedate varchar(50) out
as
set nocount on

select
    @message =
        case
            -- Failed Login
            when t.UserCode is null then 'Incorrect username or password.'
            -- User expired
            when t.PasswordExpirationDate < getdate() then 'Password expired.'
            -- Success
            else 'Success.'
        end,
    @somedata = t.somedata
from
    (select a = 1) as dt
    left join dbo.Table1 t
        on (t.usercode = @usercode and
            t.password = @password )
go

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources