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
Glen_DAsked:
Who is Participating?
 
chapmandewCommented:
My guess is that he isn't calling it properly.  the proc should always return some value for RetStatus
0
 
chapmandewCommented:
show me how you're calling your stored procedure.
0
 
Glen_DAuthor Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Glen_DAuthor Commented:
yes but even when I pass values, I'm not getting a return code...thx
0
 
chapmandewCommented:
are you calling it like this?

declare @ret tinyint

exec [dbo].[usp_CheckPWDate]
@User_Login  = test12345,
@User_Password = test9,
@RetStatus = @ret output

select @ret
 
0
 
BrandonGalderisiCommented:
The procedure uses an OUTPUT parameter, not a return code.  The return code would be the value of @RET in the below example (copying chap's code for sample).  @RetStatus would be the VALUE of that we are setting inside the procedure.  They SHOULD take the output parameter, not the return code.


declare @ret tinyint
,@retStatus tinyint

exec @ret = [dbo].[usp_CheckPWDate]
@User_Login  = test12345,
@User_Password = test9,
@RetStatus = @retStatus  output

select @retStatus


But if they HAVE to have the return code:

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
 
return @RetStatus

Open in new window

0
 
Glen_DAuthor Commented:
OK...great....I keep getting a 2 now when the PW Date is today 2/13...any ideas?

Thx
0
 
chapmandewCommented:
i guess it depends on the year you're passing in as well as your system date on yoru machine.
0
 
Glen_DAuthor Commented:
The datediff call is working fine....
0
 
chapmandewCommented:
what values are you passing to it?
0
 
BrandonGalderisiCommented:
Also, so that you don't get back into the same problem as earlier this week, didn't we remove @user_password from this procedure since it's called from the login procedure now which validates the password?

As far as returning 2.  Are you sure that it's 2/13/2009 and not 2008?  Because @password_check will be 0 which is less than <84.  For debugging, I added a select of @password_check as 'Password Age' to the following procedure.
ALTER PROCEDURE [dbo].[usp_CheckPWDate]
@User_Login varchar(50) = ,
@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())
select @Password_Check as 'PASSWORD AGE'
 
if @password_check < 84
   set @retstatus=0
else if @password_check between 85 and 90
   set @retstatus=1
else
   set @retstatus=2
 
return @RetStatus

Open in new window

0
 
Glen_DAuthor Commented:
2/11/2009 12:00:00 AM = Date_PW_Changed
0
 
BrandonGalderisiCommented:
What about this?

select DATEDIFF(day, '2009-02-13', getdate())
0
 
Glen_DAuthor Commented:
returns 0 - which is good

that's what I need outputted
0
 
Glen_DAuthor Commented:
password age = NULL
0
 
BrandonGalderisiCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.