SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
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 Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 03/11/2013 :  08:49:27  Show Profile  Reply with Quote
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
52309 Posts

Posted - 03/11/2013 :  12:22:45  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 03/11/2013 :  12:41:41  Show Profile  Reply with Quote
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

260 Posts

Posted - 03/14/2013 :  07:12:02  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/14/2013 :  07:16:14  Show Profile  Reply with Quote

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
Go to Top of Page

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 03/14/2013 :  10:09:51  Show Profile  Reply with Quote
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

Edited by - Grifter on 03/14/2013 10:23:08
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 03/14/2013 :  11:37:14  Show Profile  Reply with Quote
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

260 Posts

Posted - 03/15/2013 :  09:14:19  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/15/2013 :  11:26:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000