I have two tables, where only one column is the same. I am trying to write a stored procedure which pulls a value from a passed-in column name, but one which only exists in one of the tables. This is what I have written:
IF EXISTS(
SELECT identifier FROM TableA WHERE identifier='12345')
SELECT ColumnWhichOnlyExistsInA FROM TableA WHERE identifier='12345'
ELSE
SELECT ColumnWhichOnlyExistsInA FROM TableB WHERE identifier='12345'
This gives me the 'Invalid column name' error on the last line, even though the IF statement is true and it should only be pulling stuff from TableA. I've noticed that if I replace the bottom two SELECT statements with stored procedures which do the same thing, it works; SQL doesn't try to execute the one where IF is false.
I want to keep everything in one stored procedure. What would be the best way of doing it?
Your query is failing to COMPILE because the compiler checks that columns to be selected exist in the respective tables - so the IF statement never gets executed.
It works in separate stored procedures because these are compiled on demand at runtime, and thus only the "working" stored proc would be compiled.
To get around the problem use Dynamic SQL
DECLARE @SQLString varchar(max)
IF EXISTS (SELECT idenfier FROM TableA WHERE identifier='12345')
BEGIN
SET @SQLString = 'SELECT ColumnWhichOnlyExistsInA FROM TableA WHERE identifier=''12345'''
END
ELSE
BEGIN
SET @SQLString = 'SELECT ColumnWhichOnlyExistsInA FROM TableB WHERE identifier=''12345'''
END
EXECUTE sp_executesql @SQLString
Have you tried querying the schema as your first test? Eg
select table_name from information_schema.columns where column_name='ColumnWhichOnlyExistsInA'
before the SQL can be run , the parser will try to parse the SQL which also means that it will try to check if all the reference columns are in the table or not
for example the below will not work coz there is no column by name name1 in the table sys.table
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='foo')
SELECT * from sys.objects where name= 's'
ELSE
SELECT * from sys.tables where name1= 's'
So if TableB does not have identifier as a column the error will come up
