MS SQL select inside an if

[jseko]

I am attempting to write the below query, but the column doesn't exist in photos table so MS SQL is reporting an error

if exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'audio' and column_name = 'aud_length')
	begin
	select * from audio where aud_length = 334
	end
if exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'photos' and column_name = 'aud_length')
	begin
	select * from photos where aud_length = 334
	end

 

[AlienKing]

Right, it's still parsing that query inside the if statement and finding that it's not valid before running the if statement.

Something like this might work.

if exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'audio' and column_name = 'aud_length')
	begin
	EXEC sp_executesql @statement = N'select * from audio where aud_length = 334'
	end
if exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'photos' and column_name = 'aud_length')
	begin
	EXEC sp_executesql @statement = N'select * from photos where aud_length = 334'
	end

 

[mikeblas]

Yes, you have to defer the execution past execution preparation because execution preparation includes binding names.

Seems like a pretty goofy thing to do, tho. Why not create the missing column, or create a view that contains the missing column, or ... ?

 

[Zangmonkey]

If you cannot create the missing column then you should make it conditional on a statement which returns the column names in the specified table.

EDIT:

Something like this:

IF EXISTS (select column_name from information_schema.columns
where table_name = 'YourTableName' and column_name ='QuestionableColumn')