asked on

Set Status - If Then Else

I have this sp that allowing a user to update his/her profile. If the user changes his or her Program or Project then the status gets reset to 2.

set @S = (select [status] from tbl_User where UserID = @UserID)

UPDATE U
SET
u.User_Login = @User_Login,
u.User_Password = @User_Password,
u.User_Email = @User_Email,
u.Password_Hint = @Password_Hint,
if @Assigned_Program <> @S
THen u.status = 2
else
if @assigned_Project <> @S
Then u.Status = 2

FROM tbl_User u
WHERE userid = @userid

Glen_D

ASKER

Whoops...haven't had enough coffee yet...updated issue below..thx

set @S = (select [status] from tbl_User where UserID = @UserID)

UPDATE U
SET
u.User_Login = @User_Login,
u.User_Password = @User_Password,
u.User_Email = @User_Email,
u.Password_Hint = @Password_Hint,
if @Assigned_Program <> Assigned_Program
THen u.status = 2
else
if @assigned_Project <> Assigned_Project
Then u.Status = 2

FROM tbl_User u
WHERE userid = @userid

ASKER CERTIFIED SOLUTION

pssandhu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glen_D

ASKER

The sp is not storing the Program, Project or Center variables...the user may only select one of these so the CASE clause has to account for this...thx

ALTER PROCEDURE [dbo].[usp_UpdateUserProfile]
@UserID1 int = 2,
@userID int = '1',
@User_Login varchar(50)= 'jsmith11',
@User_Password varchar (50) = '1Tennesseebest',
@Last_Name varchar (50) = 'Smith',
@First_Name varchar (50)= 'John',
@Office_Phone varchar (12)= '1234567890',
@Alternate_Phone varchar (12)= '9999999999',
@Role varchar (50)= '3',
@Assigned_Program int = '2',
@Assigned_Project int = '2',
@Assigned_Center int = '2',
@Work_Address varchar (50)= 'abc street',
@Work_City varchar (50)= 'chicago',
@Work_State varchar (2)= 'il',
@Work_Zipcode varchar (9)= '60412',
@User_Email varchar (250)= 'some@gmail.com',
@Password_Hint varchar (50)= NULL

AS

BEGIN

SET NOCOUNT ON;

UPDATE U
SET
u.User_Login = @User_Login,
u.User_Password = @User_Password,
u.User_Email = @User_Email,
u.Password_Hint = @Password_Hint,
u.[status] = CASE WHEN r.Assigned_Program <> @Assigned_Program Then 2
WHEN r.Assigned_Project <> @assigned_Project Then 2
WHEN r.Assigned_Center <> @Assigned_Center Then 2
Else u.[status]
END
FROM tbl_User u join tbl_Registration r on u.UserID = r.UserID
WHERE u.userid = @userid

UPDATE r

SET
r.Last_Name = @Last_Name,
r.First_Name = @First_Name,
r.Office_Phone = @Office_Phone,
r.Alternate_Phone = @Alternate_Phone,
r.Role = @Role,
r.Assigned_Program = @AP,
r.Assigned_Project = @APP,
r.Assigned_Center = @C,
r.Work_Address = @Work_Address,
r.Work_City = @Work_City,
r.Work_State = @Work_State,
r.Work_Zipcode = @Work_Zipcode
FROM dbo.tbl_Registration r
WHERE userid = @userid

END

Glen_D

ASKER

My bad....i had some old variable sin there...thx