September 28, 2011 at 6:01 am
I need to bridge versions of SQL Server because I have to support versions from 2000 through 2008. I have scripts where I need to run one query for SQL 2000 and another for SQL 2005+. I use a simple If Else statement like this:
Declare @sqlVersion numeric(4,2)
Select @sqlVersion = left(cast(serverproperty('productversion') as varchar), 4)
If @SQLVersion >= 9
This works well and runs the necessary query, depending on the version of SQL Server found.
This time I have run into a situation where the Else part of the statement is not being executed and I cannot figure out why. Here is the whole statement:
Declare @sqlVersion numeric(4,2)
Select @sqlVersion = left(cast(serverproperty('productversion') as varchar), 4)
If @SQLVersion >= 9
if exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
Else
if exists (select 1 from master..syslogins where name = 'pos_reporting')
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
The "If" part of the statement works like a charm every time. It works no matter which version I am testing for in the If part.
The Else part never works, no matter what I am testing for in the If part.
Note, I realize that more tests should be run to make sure the user doesn't exist before trying to create it, but that come after I work out this issue.
I have tried enclosing the Inner If statements with Begin and End but it makes no difference, the If part always works and the Else part never works.
If I change the Else part to another If, like: If @SQLVersion < 9
it will work.
All I ever get back from SQL is "Command(s) completed successfully.", no actual help there.
I am running this from SSMS 2008 R2, connecting to a SQL 2005 and SQL 2000 server to test that it works for both pieces of code.
What am I missing about If Else behavior?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
September 28, 2011 at 6:39 am
This may seem silly, but does this: -
SELECT 1
FROM master..syslogins
WHERE NAME = 'pos_reporting'
Return anything on the server where the ELSE statement is failing?
September 28, 2011 at 7:27 am
The problem is due to your if structure.
you have this
If @SQLVersion >= 9
if exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
Else
if exists (select 1 from master..syslogins where name = 'pos_reporting')
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
You need to put some begin end blocks in there. Your else statement here belongs to the second if ( if exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')) which I don't think is what you want.
I think you want this instead.
If @SQLVersion >= 9
begin
if exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
end
Else
if exists (select 1 from master..syslogins where name = 'pos_reporting')
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
This is why I make it a habit of using begin end for if statements. Avoids all the confusion.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 8:46 am
I originally tried the Begin End for both sides of the If Else, as it is my understanding that if you do not use them SQL will only execute one statement after the If or Else statements, and I have two. When it did not work for the Else part I began trying variations to see if I could make it work, and because the If part always worked and the Else part never worked, regardless of the presence of Begin End, I left them out for the iteration I posted.
I don't know why I couldn't get it to work, but it works now, with just the addition of the Begin End around each of the inner If's.
Declare @sqlVersion numeric(4,2)
Select @sqlVersion = left(cast(serverproperty('productversion') as varchar), 4)
If @SQLVersion >= 9
Begin
If exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
End
Else
Begin
If exists (select 1 from master..syslogins where name = 'pos_reporting')
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
End
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
September 28, 2011 at 8:51 am
I will reformat this in c style blocking so you can see what was happening with the structure the way you had it.
If @SQLVersion >= 9
{
If exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
{
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
}
Else
{
If exists (select 1 from master..syslogins where name = 'pos_reporting')
{
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
}
}
}
Does that help explain what was happening. You are correct that without begin end blocks it will execute the next line of code. But in your case you wanted the Else to belong to the first if statement but your else belonged to the second one and there was no else for you main if. Wow that is a confusing sentence. :w00t:
Anyway, I hope that helps you understand why it now works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 10:01 am
Sean Lange (9/28/2011)
I will reformat this in c style blocking so you can see what was happening with the structure the way you had it.
Since you didn't include your code in a CODE block, it removed all of your indenting. I find that the indenting is the most useful feature for seeing the structure of the code, so the fact that your post lacked any visible indenting made it more confusing rather than less.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2011 at 10:05 am
drew.allen (9/28/2011)
Sean Lange (9/28/2011)
I will reformat this in c style blocking so you can see what was happening with the structure the way you had it.Since you didn't include your code in a CODE block, it removed all of your indenting. I find that the indenting is the most useful feature for seeing the structure of the code, so the fact that your post lacked any visible indenting made it more confusing rather than less.
Drew
/facepalm
Guess that is what I get for not looking at my post.
If @SQLVersion >= 9
{
If exists (select 1 from master.sys.sql_logins where name = 'pos_reporting')
{
EXEC dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
}
Else
{
If exists (select 1 from master..syslogins where name = 'pos_reporting')
{
Exec dbo.sp_grantdbaccess @loginame = N'pos_reporting', @name_in_db = N'pos_reporting'
}
}
}
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 12:09 pm
You would normally be right Stamey.
The problem is that you have a double-nested IF statement.
So while normally lets say your code was written like this:
IF A = B
SELECT 1
SELECT 2
You wouldn't need to put a BEGIN-END around the SELECT 1 statement, since the IF statement will by default only execute the line immediately after it, unless a BEGIN-END block surrounds multiple lines.
The problem is the ELSE statement. Sean's code explains it nicely. When you have an ELSE in there, with two IFs, it will go with the one closest to it in depth.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply