Solved

Set Status - If Then Else

Posted on 2009-06-29
4
240 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:Glen_D
  • 3
4 Comments
 

Author Comment

by:Glen_D
ID: 24735544
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
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24735592
I am guessing you are looking for the correct syntax for IF..THEN..ELSE
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,
u.status = CASE WHEN Assigned_Program <> @Assigned_Program Then 2
                WHEN Assigned_Project <> @assigned_Project Then 2
                Else u.status
           END 
FROM tbl_User u
WHERE userid = @userid

Open in new window

0
 

Author Comment

by:Glen_D
ID: 24735702
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
0
 

Author Comment

by:Glen_D
ID: 24735812
My bad....i had some old variable sin there...thx
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question