?
Solved

Set Status - If Then Else

Posted on 2009-06-29
4
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 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