SQL Server Forums - Using begin...end and IF in procedure

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

Using begin...end and IF in procedure

New Topic

Reply to Topic

Printer Friendly

Author

Topic

Grifter
Posting Yak Master

214 Posts

Posted - 03/11/2013 : 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

Edited by - Grifter on 03/11/2013 08:51:30

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

Posted - 03/11/2013 : 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/

Edited by - visakh16 on 03/11/2013 12:44:04

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

Posted - 03/11/2013 : 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

Grifter
Posting Yak Master

214 Posts

Posted - 03/14/2013 : 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

Edited by - Grifter on 03/14/2013 07:14:15

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts

Posted - 03/14/2013 : 07:16:14


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

--
Chandu

Edited by - bandi on 03/14/2013 07:16:45

Grifter
Posting Yak Master

214 Posts

Posted - 03/14/2013 : 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


                        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

Edited by - Grifter on 03/14/2013 10:23:08

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

Posted - 03/14/2013 : 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

Grifter
Posting Yak Master

214 Posts

Posted - 03/15/2013 : 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

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

Posted - 03/15/2013 : 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/

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.08 seconds.