February 4, 2008 at 3:20 am
I have this script:
IF NOT EXISTS (
SELECT * FROM syscolumns
WHERE object_name(id) = 'DcReservationContact' AND name = 'CostCode')
BEGIN
PRINT 'Add CostCode into DcReservationContact'
ALTER TABLE DcReservationContact ADD CostCode VARCHAR (50) NULL
END
GO
IF EXISTS (
SELECT * FROM syscolumns
WHERE object_name(id) = 'DcReservationContact' AND name = 'CostCenter')
BEGIN
UPDATE DcReservationContact
SET CostCode = CostCenter
WHERE CostCenter IS NOT NULL
END
GO
Later, the Column CostCenter is deleted from the DcReservationContact table with another script.
I mention that all the scripts are launched when they are created.
The problem is that all the scripts have to be run once again all together at the end.
And at this point there will be an error in the second script:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'CostCenter'.
Why this error appears, if there is the if exists() clause to check if the CostCenter column exists?
Why the sql server bypass this if clause (even if the code in the if clause is not executed if the test is not true)?
February 4, 2008 at 9:18 am
Unfortunatly I can't duplicate the error so I can't be 100% sure but you could be running into a compile error. I've had the problem before where even though I am using an "IF EXISTS" the compiler checks for the existince of the column and when it can't find it throws an error. The rest of your code will continue because of the "GO" statement.
You could try moving the "UPDATE DcReservationContact" code into dynamic SQL and see if it will run that way.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 4, 2008 at 9:47 am
Thanks Kenneth for your reply,
I found the solution for my problem, you were right: executing the Update statement with dynamic SQL resolved the issue.
I read somewhere that the SQL Server raises these errors while preprocessing the batch, which are similar to those that are raised when a normal select tries to access a nonexistent column.
So the solution would be:
IF EXISTS (
SELECT 1 from syscolumns WHERE object_name(id) = 'DcReservationContact' and name = 'CostCenter')
BEGIN
exec('
UPDATE DcReservationContact
SET CostCode = CostCenter
WHERE CostCenter IS NOT NULL
')
END
GO
Because the execute immediate statement is run only if the if exists() function succeeds, the Server does not raise any errors when it compiles this script.
February 4, 2008 at 10:19 am
Yea, Its kind of anoying but SQL checks the syntax for the whole thing before starting .. and since the field doesn't exist yet it throws the error.
You may want to look into sp_ExecuteSQL instead of just EXEC though. It is supposed to perform better.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 5:50 am
Not that it is significant in this case, but a very minor change will improve the performance.
IF EXISTS (
SELECT 1 from syscolumns WHERE ID = object_id('DcReservationContact') and name = 'CostCenter')
BEGIN
exec('
UPDATE DcReservationContact
SET CostCode = CostCenter
WHERE CostCenter IS NOT NULL
')
END
GO
-- This is 15x more painful
SELECT 1 from syscolumns WHERE object_name(id) = 'DcReservationContact' and name = 'CostCenter'
-- Than this is.
SELECT 1 from syscolumns WHERE ID = object_id('DcReservationContact') and name = 'CostCenter'
The reason is that you are forcing a function on an indexed column. You should always apply the function to the CONSTANT if possible. And if it can't be applied to a constant, apply it to the non-indexed column. Applying it to the PK is the worst possible choice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply