IF Statement Behaving Strangely.

[grnzbra]

I have the following code. When I run it, I get error messages saying that various fields for which the view is looking don't exist. I don't get any of the print statements. If I comment out the INSERT and SELECT statements, it runs fine but only prints the "That's all folks!" statement. The entire section should be skipped, which is what seems to be happening when the INSERT and SELECT statements are commented out. Why might this be happening?
Oh, I forgot. If I change the view to ...02, which will run correctly, it doesn't run at all; it just goes straight to "That's all folks!"

DECLARE @VersionNumber	INT
SET @VersionNumber = 2

DELETE dbo.Warranty
	IF @VersionNumber < 2
	BEGIN
		PRINT '        Version < 2'
		PRINT '        ' + CAST(@VersionNumber AS CHAR(1))		PRINT 'YOU WENT DOWN THE WRONG PATH!'
		INSERT INTO dbo.Warranty
		SELECT * FROM dbo.vwCMMSSrcWarranty00
	END
PRINT 'That''s all folks!'

 

[gmmastros]

Sounds to me like the view does not have the same columns that the table has. Personally, I would never allow code like this in to my database because it is (in my opinion) brittle. I'm specifically referring to the insert/select part. You should really list the column names, like this...

		INSERT INTO dbo.Warranty[!](Column1, Column2, ColumnX)[/!]
		SELECT [!]Column1, Column2, ColumnX [/!]FROM dbo.vwCMMSSrcWarranty00

By specifying the columns, you are making the code less susceptible to breaking whenever someone modifies the view.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grnzbra]

You're right. It doesn't have the same columns as the table. This section of code (within the IF statement) is not supposed to run.

 

[Olaf Doschke]

While the IF branch doesn't run as 2<2 is FALSE, the compilation of scripts still does make a check about fields and view and table structures.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Andrzejek]

Also, shouldn't:
[tt]DELETE dbo.Warranty[/tt]
be[tt]
DELETE [highlight #FCE94F]FROM[/highlight] dbo.Warranty[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[grnzbra]

Ouch. Is there any way of stopping the check from being made?

DELETE FROM is what I always used until I started working here. The standard here seems to be just DELETE.

 

[Olaf Doschke]

...stopping the check being made...

Not using SSMS, I guess.

If you send such scripts from a client app or use sqlcmd.exe to execute that, the compilation might not check, but I haven't tested that, might be deeper and part of the database engine and not just the SSMS query editor.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Andrzejek]

You're right. It [dbo.vwCMMSSrcWarranty00] doesn't have the same columns as the table" [dbo.Warranty] when VersionNumber < 2

So, does it mean that when VersionNumber = 2 or more, dbo.vwCMMSSrcWarranty00] does have the same columns as the dbo.Warranty table?


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

No, Andy, if the version is lower than 2, the code runs. The structure of either dbo.Warranty exclusive or dbo.vwCMMSSrcWarranty00 changed with version 2 and the code then becomes unnecessary, it seems. Perhaps the upgrade to DB version 2 makes the Warranty table obsolete and it can stay empty, then.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[grnzbra]

Versions of the spreadsheet > 1 have fewer columns because the original version of the workbook had one Warranty record for each Equipment record which meant that there could multiple records for each warranty. After version 1, whey went to a linking sheet to join the Equipment sheet to the Warranty sheet. This meant that the version 2 Warranty sheet had fewer columns. In order to avoid having to tell the script which version was in the linked server, there are two views - one which selects all the fields from the spreadsheet (version 00) and one that selects all of the fields from the spreadsheet plus three dummy columns. Which view is used is determined by the version, which the code gets from a cell on the Main sheet of the Workbook.
I'm beginning think that the way to do this would be to create a proper @SQLString statement, controlled by the IF statement and run that.

 

[Olaf Doschke]

If that's the only use case it's simpler to make the if/else branch outside, indeed, or via dynamic SQL only executing the valid part. Assignments of non-working SQL can always be done before it's executed it's just a string to the server and that has no rules.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name