[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Set Status - If Then Else

Posted on 2009-06-29
4
Medium Priority
?
266 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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