[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
Glen_D
Asked:
Glen_D
  • 7
  • 5
  • 4
4 Solutions
 
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
 
chapmandewCommented:
My guess is that he isn't calling it properly.  the proc should always return some value for RetStatus
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now