mercredi 19 décembre 2018

SQL Procedure behavior while having multiple if statements

I have stored procedure like this,

Create Procedure [dbo].[Get_Data](
@Id as Varchar(20),
@Type as Varchar(10)
)
As
Begin
  IF(@Type = 'skill')
  Begin 
   .....
   select * ....
  END

  IF(@Type = 'agent')
  Begin 
   .....
   select * ....
  END

  IF(@Type = 'skillProfile') 
  Begin 
    Print 'abc'
    select * ....
   .....
  END
END

Note: there is no syntax or any other error inside any of the if loops as every select query inside each loops are tested successfully.

So now, When i try to execute procedure using command as below,

EXEC  [Get_Data] '1391520','skillProfile'

the statement print is not printed also dint get any rows in return, instead i get message Commands completed successfully.

I tried changing last(i.e. here third) if loop statement i.e. from IF(@Type = 'skillProfile') to IF(@Type = 'profile') and tried executing like EXEC [Get_Data] '1391520','profile' which worked all fine!

Got confused with this and tried changing first if loop and last(third) if loop like this, IF(@Type = 'skill') to IF(@Type = 'xyz') and IF(@Type = 'skillProfile') to IF(@Type = 'xyzProfile') and tried executing like EXEC [Get_Data] '1391520','xyzProfile' which also worked all fine.

Now i am totally confused why it doesn't work when i use skill and skillProfile.

Aucun commentaire:

Enregistrer un commentaire