Glen_D
asked on
Not getting a return output variable on SP
When I run this sp, just stating that sp executed instead of displaying result. Need the value returned.
Exec usp_CheckPWDate
ALTER PROCEDURE [dbo].[usp_CheckPWDate]
@User_Login varchar(50) = test12345,
@User_Password varchar (50) = test9,
@RetStatus tinyint = NULL output
as
set nocount on
declare @password_date datetime
declare @password_check int
declare @userID int
select @password_date = Date_PW_Change from tbl_User
where user_login=@user_login and userID = @UserID
Set @password_check = DATEDIFF(day, @password_Date, getdate())
if @password_check < 84
set @retstatus=0
else if @password_check between 85 and 90
set @retstatus=1
else
set @retstatus=2
Exec usp_CheckPWDate
ALTER PROCEDURE [dbo].[usp_CheckPWDate]
@User_Login varchar(50) = test12345,
@User_Password varchar (50) = test9,
@RetStatus tinyint = NULL output
as
set nocount on
declare @password_date datetime
declare @password_check int
declare @userID int
select @password_date = Date_PW_Change from tbl_User
where user_login=@user_login and userID = @UserID
Set @password_check = DATEDIFF(day, @password_Date, getdate())
if @password_check < 84
set @retstatus=0
else if @password_check between 85 and 90
set @retstatus=1
else
set @retstatus=2
chapmandew
show me how you're calling your stored procedure.
ASKER
I have a developer who is coding in Cold Fusion...when a user logs on, he calls this sp to check for pw expiration...
Thx
Thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes but even when I pass values, I'm not getting a return code...thx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK...great....I keep getting a 2 now when the PW Date is today 2/13...any ideas?
Thx
Thx
i guess it depends on the year you're passing in as well as your system date on yoru machine.
ASKER
The datediff call is working fine....
what values are you passing to it?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
2/11/2009 12:00:00 AM = Date_PW_Changed
What about this?
select DATEDIFF(day, '2009-02-13', getdate())
select DATEDIFF(day, '2009-02-13', getdate())
ASKER
returns 0 - which is good
that's what I need outputted
that's what I need outputted
ASKER
password age = NULL
It will return 0 if the Date_PW_Change is any time in the future and as far back as 83 days.
It will return 1 if the Date_PW_Change is 85 to 90 days old.
It will return 2 if the Date_PW_Change is 91 days old or greater.
if @password_check < 84
set @retstatus=0
else if @password_check between 85 and 90
set @retstatus=1
else
set @retstatus=2
It will return 1 if the Date_PW_Change is 85 to 90 days old.
It will return 2 if the Date_PW_Change is 91 days old or greater.
if @password_check < 84
set @retstatus=0
else if @password_check between 85 and 90
set @retstatus=1
else
set @retstatus=2