Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using begin...end and IF in procedure

Author  Topic 

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-11 : 08:49:27
Hi

Can I see some examples of when and how to use Begin and End in a SQL Server conditional statement like in a procedure? In my own example I am looking for the charindex of a certain string in a cursor data set and if it finds a value then put this value into a variable.

If that string is not in the cursor then I want to check for another string, then another if that is not available.

I also understand instead of using if then elseif you use nested IFs - is this right or am I getting mixed up?

So instead of:

If x=y then
Do somethings
Elseif x=z then
do something
Else
Do this
End if


I would say:

If x=y 
Do somethings
if x=z then
do something
Else
Do this


I remember a guy saying that you use begin end when you are doing more than one thing so my statement above should be:


BEGIN
If x=y
Do somethings
if x=z then
do something
Else
Do this
END


But If I done more than one thing within that statement I would have to use another begin end, is more than one BEGIN END including the IF statement or just where two things happen?

Like:


BEGIN
BEGIN
If x=y
Do somethings
Variable = Result
END
if x=z then
do something
Else
Do this
END


does this make sense or is it mince?

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:22:45
if you want to nested Ifs it should be like


BEGIN

If x=y
BEGIN
Do somethings
Variable = Result

if x=z then
BEGIN
do something
END
END
Else
BEGIN
Do this
END
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-11 : 12:41:41
BEGIN/END is simply a statement enclosure. The parser will allow a wide variety of usage.
Best practice (for me anyway) is to always use BEGIN/END for things like IF and WHILE. Anywhere that the statement execution means something different if it is not grouped. Explicit use of BEGIN/END will remove any ambiguity as to the developer's intentions.

so

if <condition>
begin
<one or more statements>
end
else
begin
if <condition>
begin
<one or more statements>
end
end



Be One with the Optimizer
TG
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-14 : 07:12:02
Ok maybe you can help me with this. Getting incorrect syntax near 'else' at line 50:

Just in case you're wondering what this is, I have a main string
where I am looking for substring1; substring1 can be either on it's own,
included in another word substring2 within the main string,
or both within substring2 and on its own in main string.

--If string 1 is present, string 2 may be present and string 1 may be in it
if CHARINDEX('string1',@Dept_Name) > 0  
BEGIN
SET @MyVar = CHARINDEX('string1',@MyVar )
SET @MyVar2 = CHARINDEX('string2',@MyVar )
PRINT 'String 1 Position ' + CONVERT(VARCHAR(5), @MyVar )
PRINT 'String 2 Position ' + CONVERT(VARCHAR(5), @MyVar2)

--If the string 1 is in string 2 this check will equal 5 because that is the difference between the start of string 2 and the start of string 1 if it is contained in string 2
if @MyVar - @MyVar2 = 5 <<< line 50
BEGIN
insert into dbo.test([name])values('String name ' + @MyVar + ' has both String1 and String2 in it')
-- if the check is false then it means only string1 is present
else
insert into dbo.test([name])values('Clinic name ' + @MyVar + ' only has string1 in it')

END
else
BEGIN
insert into dbo.Test([Name])VALUES(@Clinic_Name + ' Doesn''t have any of the strings in it')
END
END
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:16:14
[code]
if CHARINDEX('string1',@Dept_Name) > 0
BEGIN
SET @MyVar = CHARINDEX('string1',@MyVar )
SET @MyVar2 = CHARINDEX('string2',@MyVar )
PRINT 'String 1 Position ' + CONVERT(VARCHAR(5), @MyVar )
PRINT 'String 2 Position ' + CONVERT(VARCHAR(5), @MyVar2)

--If the string 1 is in string 2 this check will equal 5 because that is the difference between the start of string 2 and the start of string 1 if it is contained in string 2
if @MyVar - @MyVar2 = 5 <<< line 50
BEGIN
insert into dbo.test([name])values('String name ' + @MyVar + ' has both String1 and String2 in it')
-- if the check is false then it means only string1 is present
END
else
insert into dbo.test([name])values('Clinic name ' + @MyVar + ' only has string1 in it')

END
else
BEGIN
insert into dbo.Test([Name])VALUES(@Clinic_Name + ' Doesn''t have any of the strings in it')
END
[/code]

--
Chandu
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-14 : 10:09:51
Still getting that error on this piece of code?

if (@myvar1-@myvar1) = 5
BEGIN
insert into dbo.test([name])values('String ' + @myvar1 + ' has both string1 and string2 in it')
else
insert into dbo.test([name])values('String ' + @myvar1 + ' only has string2 in it')
END


Not sure if it is above or:


if (@myvar1-@myvar1) = 5
BEGIN
insert into dbo.test([name])values('String ' + @myvar1 + ' has both string1 and string2 in it')
else
BEGIN
insert into dbo.test([name])values('String ' + @myvar1 + ' only has string2 in it')
END
END


Or


if (@myvar1-@myvar1) = 5
BEGIN
insert into dbo.test([name])values('String ' + @myvar1 + ' has both string1 and string2 in it')
END
else
BEGIN
insert into dbo.test([name])values('String ' + @myvar1 + ' only has string2 in it')
END



But every one of these fails

The whole code structure is:


IF Condition
--do things
BEGIN
IF Condition
BEGIN
--do things
END
ELSE
BEGIN
--Do things
END
ELSE
BEGIN
--Do things
END
END
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-14 : 11:37:14
Each "--do things" must be in its own begin/end block. So:

IF <Condition>
BEGIN
--do things

IF <nested Condition>
BEGIN
--do nested things
END
ELSE
BEGIN
--Do other nested things
END
END
ELSE
BEGIN
--Do other things
END


Be One with the Optimizer
TG
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-15 : 09:14:19
I had an END after the Outer ELSE statement as I thought the BEGIN END for the first IF had to be around the whole inner statement

So instead of:


IF <Condition>
BEGIN
--do things

IF <nested Condition>
BEGIN
--do nested things
END
ELSE
BEGIN
--Do other nested things
END
END
ELSE
BEGIN
--Do other things
END


I had


IF <Condition>
BEGIN
--do things

IF <nested Condition>
BEGIN
--do nested things
END
ELSE
BEGIN
--Do other nested things
END

ELSE
BEGIN
--Do other things
END

END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-15 : 11:26:32
the latter should have been this

IF <Condition>
BEGIN
--do things

IF <nested Condition>
BEGIN
--do nested things
END
ELSE
BEGIN
--Do other nested things
END
END
ELSE
BEGIN
--Do other things
END


otherwise you will be left with two ELSE for same IF which is invalid



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -