Solved

Set Status - If Then Else

Posted on 2009-06-29
4
224 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now