April 22, 2010 at 10:23 am
Hi,
I have a query that is run against several tables and databases.
I check if a column exist in a table before selecting values from it.
The if is always the same in the following example, but the select statement is different.
If the table and column exist in the table, it doesn't error out.
If the table exist, but not the column, I get an error.
If the table doesn't exist, it doesn't error out.
Sample table:
create table mytable (
colA varchar(50),
colB varchar(40)
)
This query doesn't return anything, and it doesn't have errors (OK)
Declare @test-2 varchar(500)
if exists (
select 1 from sysobjects so inner join syscolumns sc on sc.id=so.id
where so.name = 'mytable' and sc.name = 'colC'
) begin
select @test-2 = @test-2 + colB from mytable
end
This query return an error, invalid column name ColC, even if I've checked for it existence...
if exists (
select 1 from sysobjects so inner join syscolumns sc on sc.id=so.id
where so.name = 'mytable' and sc.name = 'colC'
) begin
select @test-2 = @test-2 + colC from mytable
end
This query don't return anything, and it doesn't have errors (OK)
if exists (
select 1 from sysobjects so inner join syscolumns sc on sc.id=so.id
where so.name = 'mytable' and sc.name = 'colC'
) begin
select @test-2 = @test-2 + colC from mytable2
end
How can I work around the error with the second query? It is a bug from Microsoft?
April 22, 2010 at 12:18 pm
Isn't that just a parse error that simply won't allow it to run because it's a non-existent object being referenced?
You can get around it with dynamic sql, but there may be cleaner ideas.
What are you trying to do? Maybe there is just a different way to go about it.
April 22, 2010 at 12:32 pm
Hi bteraberry,
Unfortunately, this is not a parser error. If you put that sql statement in a SQL job, even if encapsulated in an exec statement, it throw an error and stop the job execution there.
My target was to scan every user database on a server, grabing some information from specific tables and column, if possible. Databases belong usually to the same software, but are in different versions, some have tables and columns that other doesn't. Temp variables are used to store the info.
This information is then stored into a reference table. It kinda catalog every database we have, listing some parameters stored into a params table.
April 22, 2010 at 2:45 pm
Meta data will be checked for existing objects which is why your only parse failure ocurrs when the table exists, but a column does not exist. As bteraberry suggested you will need to issue dynamic SQL for the select statement after checking for existence of the table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply