January 11, 2007 at 12:59 pm
For some reason this code works in one example but not the other! In the examples below, the column 'MY_COLUMN' does not exist, but if it did, I would like to check that it does and perform some work. When I try to perform multiple statements wrapped by a BEGIN and END I get the error. Why? How do I modify this so example 2 will work? See this code:
1 --EXAMPLE 1
2 if exists (select c.name from dbo.syscolumns c, dbo.sysobjects o
3where c.id=o.id
4and c.name='MY_COLUMN'
5and o.id = object_id(N'[dbo].[MY_TABLE]')
6and OBJECTPROPERTY(o.id, N'IsUserTable') = 1)
7 print 'true'
8 else
9 print 'false'
10 -- prints 'false' as expected, column is not there. When I change MY_COLUMN to a column that I know does exist, it prints 'true' as expected.
11
12 -- EXAMPLE 2
13 IF EXISTS (select c.name from dbo.syscolumns c, dbo.sysobjects o
14where c.id=o.id
15and c.name='MY_COLUMN'
16and o.id = object_id(N'[dbo].[MY_TABLE]')
17and OBJECTPROPERTY(o.id, N'IsUserTable') = 1)
18 BEGIN
19SELECT PRIMARY_KEY_COLUMN, MY_COLUMN + SOME_OTHER_COLUMN as MY_NEW_COLUMN
20INTO #MY_TEMP_TABLE
21FROM MY_TABLE
22
23UPDATE MY_TABLE
24SET MY_TABLE.SOME_OTHER_COLUMN = #MY_TEMP_TABLE.MY_NEW_COLUMN
25FROM MY_TABLE, #MY_TEMP_TABLE
26WHERE MY_TABLE.PRIMARY_KEY_COLUMN = #MY_TEMP_TABLE.PRIMARY_KEY_COLUMN
27-- drop the temp table
28DROP TABLE #MY_TEMP_TABLE
29-- drop my column
30ALTER TABLE MY_TABLE
31DROP COLUMN MY_COLUMN
32 END
33 -- result is:
34 Server: Msg 207, Level 16, State 3, Line 19
35 Invalid column name 'MY_COLUMN'.
January 11, 2007 at 1:13 pm
I would try:
IF EXISTS (select c.name from dbo.syscolumns c, dbo.sysobjects o
where c.id=o.id
and c.name='MY_COLUMN'
and o.id = object_id(N'[dbo].[MY_TABLE]')
and OBJECTPROPERTY(o.id, N'IsUserTable') = 1)
BEGIN
SELECT PRIMARY_KEY_COLUMN, MY_COLUMN + SOME_OTHER_COLUMN as MY_NEW_COLUMN
INTO #MY_TEMP_TABLE
FROM MY_TABLE
END
ELSE
BEGIN
UPDATE MY_TABLE
SET MY_TABLE.SOME_OTHER_COLUMN = #MY_TEMP_TABLE.MY_NEW_COLUMN
FROM MY_TABLE, #MY_TEMP_TABLE
WHERE MY_TABLE.PRIMARY_KEY_COLUMN = #MY_TEMP_TABLE.PRIMARY_KEY_COLUMN
-- drop the temp table
DROP TABLE #MY_TEMP_TABLE
-- drop my column
ALTER TABLE MY_TABLE
DROP COLUMN MY_COLUMN
END
CY
January 11, 2007 at 1:20 pm
Thanks for taking a look. This version still returns the same error. Very strange.
January 11, 2007 at 2:11 pm
T-SQL Parsing happens first. Then execution.
If the column does not exist, the parser barfs on the ALTER TABLE statement at *parse* time, before any of the code has actually executed.
You need to build the alter statement in dynamic SQL and execute it, so that it doesn't get parsed at the same time as the IF block.
[Edit] Also, you may want to look at using INFORMATION_SCHEMA.COLUMNS, instead of hitting the sys* tables. Future-proofs your code and already joins the necessary underlying system tables.
January 11, 2007 at 2:20 pm
I'll give it a shot, thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply