Author |
Topic |
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-03-11 : 08:49:27
|
HiCan 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 thenDo somethingsElseif x=z thendo somethingElseDo thisEnd if I would say:If x=y Do somethings if x=z then do somethingElseDo 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:BEGINIf x=y Do somethings if x=z then do somethingElseDo thisEND 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 somethingElseDo thisEND 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 likeBEGIN If x=y BEGIN Do somethings Variable = Result if x=z then BEGIN do something END ENDElseBEGINDo thisENDEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. soif <condition>begin <one or more statements>endelsebegin if <condition> begin <one or more statements> endend Be One with the OptimizerTG |
|
|
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 itif 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') ENDelse BEGIN insert into dbo.Test([Name])VALUES(@Clinic_Name + ' Doesn''t have any of the strings in it') ENDEND |
|
|
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') ENDelseBEGIN insert into dbo.Test([Name])VALUES(@Clinic_Name + ' Doesn''t have any of the strings in it')END[/code]--Chandu |
|
|
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 failsThe whole code structure is:IF Condition --do thingsBEGIN IF Condition BEGIN --do things END ELSE BEGIN --Do things ENDELSE BEGIN --Do things ENDEND |
|
|
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 ENDENDELSEBEGIN --Do other thingsEND Be One with the OptimizerTG |
|
|
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 statementSo instead of:IF <Condition>BEGIN --do things IF <nested Condition> BEGIN --do nested things END ELSE BEGIN --Do other nested things ENDENDELSEBEGIN --Do other thingsEND I had IF <Condition>BEGIN --do things IF <nested Condition> BEGIN --do nested things END ELSE BEGIN --Do other nested things ENDELSEBEGIN --Do other thingsENDEND |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-15 : 11:26:32
|
the latter should have been thisIF <Condition>BEGIN --do things IF <nested Condition> BEGIN --do nested things END ELSE BEGIN --Do other nested things ENDENDELSEBEGIN --Do other thingsEND otherwise you will be left with two ELSE for same IF which is invalid------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|