July 12, 2010 at 11:00 am
This is annoying and I can't seem to find a solution I like. At my workplace, we have to build our scripts with the idea that they might be run multiple times in our Test environment. Unfortunately, when we do scripts that include dropped columns, we run into problems.
Here's an example:
/*Create Table dbo.MyTable (MyID int Identity (1,1) NOT NULL, MyName char(10) NULL,
MyDate smalldatetime NULL);
GO
Insert into dbo.MyTable (MyName, MyDate)
(Select 'Mickey', GetDate() UNION ALL Select 'Mouse', GetDate()
UNION ALL Select 'Donald', GetDate() UNION ALL Select 'Duck', GetDate());*/
IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyDate') IS NOT NULL
BEGIN
Alter Table dbo.MyTable
Drop Column MyDate;
END;
Pretending that dbo.MyTable already exists (I just gave the code so people could play with it), if I re-run the IF ... IS NOT NULL statement more than once, it gives me the following error:
'Msg 207, Level 16, State 1, Line 2
Invalid column name 'MyDate'.'
Even if I uses IF EXISTS ... I get the same error. And if I took the statement out of the IF statement, I'd still get the same syntax error the next time I ran the statement.
Any thoughts, aside from using dynamic SQL, in how to resolve this problem? I want the IF statement to execute and skip the step if the column doesn't exist. Not error out before any of the code runs.
July 12, 2010 at 11:06 am
It's a parse-time error, the error's happening before anything, including the IF executes.
Referencing a table that doesn't exist is allowed, it's called deferred resolution. Referencing a column that doesn't exist in a table that does is not permitted however.
The only real option here is dynamic SQL. You need something that will only be parsed if it's going to be executed, and that's dynamic SQL (or a call to a stored proc, or a separation of batches, neither of which is particularly practical here.)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2010 at 11:25 am
Bleargh. I had a feeling that was going to be the answer.
Oh, well. If I hadn't asked the question, there would have been an alternative. @=)
Thanks, Gail.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply