If condition in Store Proc

Hi ,

I was trying to put if condition in my store proc ,it throws error. I know am doing wrong some where,can some one guide me please

Here what am trying to achieve is ,when ever i do update,i will check the existing record and get the "ClassValue" and check the parameter am getting if both are not equal then i will update "ClassValue" ="01" if not then i will pass the parameter.

Am not sure i miss something in my If condition look like


CREATE PROC [dbo].[InsertEmployee]
      @EmployeeNo int,
      @ClassValue Decimal(18,2)
AS
IF NOT EXISTS (select employeeno from Phone where employeeno = @EmployeeNo and PhoneNumber = @PhoneNumber)
BEGIN
      DECLARE @CONTEXT_INFO varchar(100)
      SELECT @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
      Declare @Level deceimal(18,2)

      SELECT
             @Level = Ad.ClassValue
      FROM
             Employee Ad
      WHERE Ad.Employeeno= @EmployeeNo

If(@Level !=Ad.ClassValue)
BEGIN
update [Employee] set ClassValue= '01', UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo 
END
 Else
Begin
update [Employee] set ClassValue= ClassValue, UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo 

End
Else
Begin	

     I will insert.
END

Open in new window


Sha1395Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
what about:
CREATE PROC [dbo].[InsertEmployee]
      @EmployeeNo int,
      @ClassValue Decimal(18,2)
AS
IF NOT EXISTS (select employeeno from Phone where employeeno = @EmployeeNo and PhoneNumber = @PhoneNumber)
BEGIN
      DECLARE @CONTEXT_INFO varchar(100)
      SELECT @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)



  update [Employee] 
     set ClassValue = CASE WHEN ClassValue = @ClassValue THEN '01' ELSE @ClassValue END
       , UpdatedBy = @CONTEXT_INFO 
  where employeeno = @EmployeeNo 

  if @@rowcount = 0
  begin
     '  I will insert.
  end
END

Open in new window

0
 
Sha1395Author Commented:
Thanks angelIII,

Please correct me if am wrong

     set ClassValue = CASE WHEN ClassValue <> @ClassValue THEN '01' ELSE @ClassValue END


Condition am trying to implement in update query is

If(ClassValue !=@classValue)
{
ClassValue ="01"
else
ClassValue=@ClassValue
}

This is what am trying.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Sha1395Author Commented:
Thanks again AngelIII for your help on yesterday and today's queries.
0
 
Sha1395Author Commented:
Hi AgnelIII,

My condition changed little bit. what if is suppose to use multiple if condition in my Query.

Like this

If(@Level ='V2') and (Ad.ClassValue='v1')
BEGIN
update [Employee] set ClassValue= '01', UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo
END
 Elseif (@Level ='V3') and (Ad.ClassValue='v2')
begin
update [Employee] set ClassValue= '01', UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo
End
Elseif (@Level ='V4') and (Ad.ClassValue='v3')
begin
update [Employee] set ClassValue= '01', UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo
End
Else
update [Employee] set ClassValue= '00', UpdatedBy = @CONTEXT_INFO where employeeno = @EmployeeNo
End
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you change the CASE syntax:
set ClassValue = CASE 
   WHEN ClassValue = 'V1' AND @ClassValue = '02' THEN '01' 
   WHEN ClassValue = 'V2' AND @ClassValue = '03' THEN '01' 
   WHEN ClassValue = 'V3' AND @ClassValue = '04' THEN '01' 
   ELSE '00' END

Open in new window

0
 
Sha1395Author Commented:
Thank you so much AngelIII
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.