Hi Dimitry
Like Sergey I like to put a BEGIN and END after my if statement even if its only one line. In fact I usually enter the BEGIN and END before I put any statements in the BEGIN END block.
I've got some pretty big stored procedures and some to the IF statement blocks are pretty big and have nested IF block in them.
>Thank you for the explanation of BEGIN/END and correction of my use of RTRIM().
>
>>1. You don't need BEGIN/END for one statement but you can wrap even one statement in BEGIN/END. I often do for consistency or if I expect to use more than one statement in the future
>>2. Yes, you have to use BEGIN/END for more than one statement otherwise only first one of the block after IF will be executed.
>>
>>BTW, LEN() function retuns length of it's parameter w/o trailing spaces so RTRIM() in
len(rtrim(@FoundValue)) is redundant. IOW
len(rtrim(@FoundValue)) returns the same result as
len(@FoundValue)>>
>>>
>>>I want to be sure I understand the use of BEGIN / END with IF statement in a stored procedure.
>>>
>>>Here is how I understand it:
>>>
>>>1. If there is only one statement after the IF condition, I don't need to have BEGIN / END. That is, this one statement after IF will be executed.
>>>
>>>2. But if there is more than one statement after IF, I must have these statements enclosed with BEGIN / END? Here is an example:
>>>
>>>
>>>if @SomeVar = 1
>>> BEGIN
>>> select @FoundValue = column_name from table1 where 'some condition'
>>> if len(rtrim(@FoundValue)) > 0
>>> set @Var2 = @FoundValue
>>> END
>>>
>>>
>>>That is, I must have the BEGIN and END above to make sure all lines after the first IF are executed only if @SomeVar =1 ?