We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Not getting a return output variable on SP

Medium Priority
357 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
show me how you're calling your stored procedure.

Author

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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
My guess is that he isn't calling it properly.  the proc should always return some value for RetStatus

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
yes but even when I pass values, I'm not getting a return code...thx
CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
are you calling it like this?

declare @ret tinyint

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

select @ret
 
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

Author

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

Thx
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
i guess it depends on the year you're passing in as well as your system date on yoru machine.

Author

Commented:
The datediff call is working fine....
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
what values are you passing to it?
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

Author

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

select DATEDIFF(day, '2009-02-13', getdate())

Author

Commented:
returns 0 - which is good

that's what I need outputted

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.