June 2, 2011 at 1:39 pm
I do not understand why this is not working.
I am checking if a column exists. IF it does I need to insert the column and other data into a table.
If the column does not exist it should do nothing.
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where table_name = 'users' and column_name = 'carrier_id')
begin
Insert into #mytable
select 'client_name' as client, getdate() as insert_date, [user_id], carrier_id
from users
where carrier_id is not null
end
This should work as far as I can tell, however when the column does not exist it still tries to run the Insert and gives error - "Invalid column name 'carrier_id'.
IF I run the following "test" code with print statements it works fine.
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where table_name = 'users' and column_name = 'carrier_id')
begin
print 'column exists'
end
else
begin
print 'column does NOT exist'
end
Please help, I must be missing something, I just don't see what it could be.
June 2, 2011 at 2:00 pm
the problem is the engine validates all objects mentioned in a script...regardless of if-then-else logic ; so if it doesn't exist at the moment it validates all objects in the query, an error is returned.
you'll have to change to dynamic SQL to do what you are after instead, i think.
Lowell
June 2, 2011 at 2:49 pm
Lowel - thank you for your reply.
I'm not quite following your idea on the possible cause.
It may also be that I have not been very clear as to the issue.
The check for the column existing seems to work just fine. I know that sometimes it will exist and other times it will not. (I have provided just 1 sample, the production version is created dynamically and runs for hundreds of DBs).
When the situation occurs that the column does not exist it still tries to run the code to insert the column to the new table. The insert naturally fails because the column I'm trying to select does not exist. (I'm not wanting to create the column if it doesn't exist).
Funny thing is, if all I am doing is printing a statement the code works fine. It seems to have a problem with the 4 line code block within the begin/end.
Any further ideas.......
Thanks much
June 2, 2011 at 3:03 pm
it's still the same issue.
the Print statement thing works because it contains no objects that resolve to database.schema.table.
The first thing the database engine does is map ALL objects in a query to a fully qualified object name.... [PRODUCTION].[dbo].[users] for example. the mapping is not controlled in any way by the logic inside the SQL Statement...so the objects are attempted to resolve BEFORE the IF..THEN...ELSE comes into play.
if it cannot map all objects, it stops right there with an error.
if it can match all objects, it goes to the next step, where it begins to evaluate the if statements, the joins, WHERE statmeents and other filters.
After that. it looks at statistics and determines what kind of joins will be used, and what execution plan to build or re-use,to get the data in the most efficient manner.
finally it executes the query, using the plan built/reused above.
you see exactly that same error if you do something like this: it runs in every database, but often, the database does not contain the object.
sp_msforEachDB 'SELECT ''?'' As DbName, Count(*) FROM CUSTOMERS '
to do what you want, your INSERT INTO...
has to be dynamic SQL, so that it is not validated agaisnt objects at compile time, but at execution timer instead.
DECLARE @sql varchar(1000)
SET @sql = 'INSERT INTO...'
IF Exists(...)
BEGIN
EXECUTE(@sql)
END
Lowell
June 2, 2011 at 3:49 pm
Ahhh....of course...following you now.
Now I have to figure out how to create this. The code is already dynamically created to get to this point as it has to run for all DBs.
So the if exists is dynamically created.
Then it has to run another exec(@sql) since it cannot run the insert statement directly.
Not sure how yet, but thanks much for the explanation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply