SQL Procedure behavior while having multiple if statements

Question

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.

HoneyBadger · Accepted Answer · 2018-12-19 13:16:17Z

3

You defined your parameter as Varchar(10), which means it cannot hold more than 10 characters. If you try to assign it a longer value, it gets truncated with no warning. So you are trying to execute your procedure with @Type='skillProfi'. The solution is to increase the size of your parameter, say varchar(100).

answered Dec 19, 2018 at 13:16

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashwathmabiyan Over a year ago

oh shit! yes you are right, i dint notice that. thanks a lot.

Sreenu131 · Accepted Answer · 2018-12-19 13:18:40Z

0

Use Try catch block you will let know where error is

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

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

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

BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

answered Dec 19, 2018 at 13:18

Sreenu131

2,5361 gold badge9 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Procedure behavior while having multiple if statements

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related