Invalid column name

I am writing sql script where I have to get colum values from table and
do some processing and then delete those column. This is a new version
of the application and we don't need those columns anymore.
But the problem is that I need to be able to run this script more then
once without any errors. So, second time when I run it, it gives an
error that Invalid column name.
Before I get values from those columns, I check if they exists or not
and then get the value, but still it gives error, so I put everything
as string and use EXEC to execute that string - but still.
I don't know what to do.
I am copying my code here.
DECLARE @.value VARCHAR(8000)
SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
exec (@.value)
The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
Anybody has any idea?
Thanks
Adnan Masood
www.newbalanceindy.comThis is just an idea, so you'll have to do the coding yourself (let me
know if that's a problem), but perhaps it would be better to not try to
do so much control flow in the dynamic sql. instead you could use the
stored procedures sp_tables and sp_columns, a couple nested of cursors
looping through these basically gives you a map of your database (not
very efficient, but dynamic without using exec). Once you've got this
you could build up a much more lightweight part of the dynamic sql,
making it far easier to debug.
As for why you're getting that error - are you regenerating the dynamic
sql on the second run? perhaps using sp_executesql will be get around
it as it will regenerate the query plan.
Cheers
Will|||Print the contents of the @.value variable and see what is wrong. If you don'
t find it, post it here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
>I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>|||Here is my code:
DECLARE @.value VARCHAR(8000)
SELECT @.value =
'DECLARE @.Dining_Mod smallint ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = 1 '
print @.value
EXEC (@.value)
Here is what I get in the print
DECLARE @.Dining_Mod smallint
IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
('DINING_ROOM_MOD_SQFT')
AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
= 1)))
SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
FROM RESTAURANT
WHERE RESTAURANT_ID = 1
...and here is the error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DINING_ROOM_MOD_SQFT'.
Because this column has been delete in the first run of the script. I
need to be able to run this as many times as I want.
Thanks
Any help will be appreciated.
Adnan Masood
http://www.newbalanceindy.com|||The problem is that when the parsing of the statement is performed, the prio
r IF statement isn't in
effect yet. So, the error that the column isn't there is returned at the par
sing stage, not
execution. Seems you have to break down this into two batches.
A more serious question would be why you need to do this check. A voilile sc
hema often indicates
problems with the data model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145295533.954382.286730@.t31g2000cwb.googlegroups.com...
> Here is my code:
> DECLARE @.value VARCHAR(8000)
> SELECT @.value =
> 'DECLARE @.Dining_Mod smallint ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = 1 '
>
> print @.value
> EXEC (@.value)
> Here is what I get in the print
> DECLARE @.Dining_Mod smallint
> IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> ('DINING_ROOM_MOD_SQFT')
> AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
> = 1)))
> SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
> FROM RESTAURANT
> WHERE RESTAURANT_ID = 1
> ...and here is the error:
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'DINING_ROOM_MOD_SQFT'.
>
> Because this column has been delete in the first run of the script. I
> need to be able to run this as many times as I want.
> Thanks
> Any help will be appreciated.
> Adnan Masood
> http://www.newbalanceindy.com
>|||IF you need to drop a column, why not remove it from this script entirely
and handle the column in a different script that will only be run once? You
really shouldnt have production code running on a regular basis for a task
that will only be done once. Run once what needs to be run once, then never
reference the code again.
I can't think of a reason to write code in a reusable procedure for a field
that is not going to exist. If you explain why you are doing this and what
this field is for you will probably get some good advice on alternative
approaches.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
> I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>