8

Is it possible to check for a column existence within an inline statement? Please note this is not a duplicate to common question "How to check if column exist." This relates to a possible INLINE solution.

There are may ways to do this but since this needs to be written for BCP [out] command I cannot use SPs and the compatibility has to match MSSQL 2000.

Would anything similar to the following have chance to work?

SELECT
[col1],
[col2],
CASE
   WHEN (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table'  AND COLUMN_NAME = 'col3') = 1
   THEN [col3]
   ELSE null
END
FROM [MyDb].[dbo].[Table]

or sub-query way like this....

    SELECT
    [col1],
    [col2],
SELECT(if exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table' AND COLUMN_NAME = 'col3') BEGIN SELECT [col3]...
Milan
  • 3,209
  • 1
  • 35
  • 46
  • @Hogan this is not a duplicate. He's not asking how to check if a column exists. He knows that. – Alex Jan 12 '16 at 20:38
  • @AlexanderMP - The first answer on the item I linked to gives syntax of how to write an if statement using exists to do what he wants. Seems like a dup to me. – Hogan Jan 12 '16 at 21:40
  • @Hogan I just wrote `IF 1=0 BEGIN SELECT bogusColumn FROM realTable END`. I did not get an empty result set, but an error. @Milan as asking about conditionally including a column in the select clause, IF it exists. But if you write it as whole SQL it throws an error when the column doesn't exist. The solution is dynamic SQL, but the question is closed. – Alex Jan 12 '16 at 21:59
  • @AlexanderMP you need an ELSE in your example and dynamic SQL is not a solution because he needs a single statement. `IF EXISTS(1) THEN SELECT A FROM T ELSE SELECT B FROM T END;` – Hogan Jan 12 '16 at 22:12
  • `IF 1=0 BEGIN SELECT bogusColumn FROM realTable END ELSE BEGIN SELECT Name FROM realTable END` still getting the same error: Msg 207. Level 16, State 1, Line 2. Invalid column name 'bogusColumn'..... And it's possible to do dynamic SQL with just 1 statement by using literals. – Alex Jan 12 '16 at 22:16
  • Hi there; Thanks you for the attention. Yes, this is not a duplicate question! I honestly could not find any solution so far for this edge case. Btw.. finishing the statement and adding ELSE block makes no difference. Any more suggestions ? Thx. – Milan Jan 12 '16 at 23:06
  • If I understand this correctly, something like this might work ? Uncomment out the print statement to debug. `code` EXEC(
    'DECLARE @sql NVARCHAR(500)
    SELECT @sql = ''select col1, col2, '' + case when exists(select * from INFORMATION_SCHEMA.columns where table_name = ''Table'' and column_name = ''col4'') then ''col4'' else ''null'' end + '' as res from [MyDB].[dbo].[Table]''
    --print @sql
    EXEC sp_executesql @sql'
    ) `code` (Sorry for formatting - replace
    with line break.)     – Moe Sisko Jan 13 '16 at 02:54
  • If this is really not a duplicate then make a new question with a link to what I marked duplicate and an explanation of why the solutions there don't work. If you make clear why they don't work then it will be clear how to answer your question to other people. (I don't believe @MoeSisko comment will work. – Hogan Jan 13 '16 at 16:15
  • @Hogan I am not sure how much more clearly I need to formulate my question. It clearly states I am looking for an "inline" solution. – Milan Jan 13 '16 at 21:34
  • the technique in my query should "work" ok (assuming formatting is fixed up), as I tested it with different table/column names. I'm not exactly sure what "inline" solution is, but if this technique doesn't work for the op, then I agree with @Hogan that it may be better to ask a new question which looks at the problem more from a BCP point of view (with the appropriate bcp tags). It may not be clear to those who are not familiar with BCP what the exact problem is. – Moe Sisko Jan 13 '16 at 23:14
  • by "work" ok - I mean it works in SSMS. As @AlexanderMP noted, dynamic sql will be required. However, if dynamic sql doesn't meet the op's requirements, then another approach from a bcp perspective may be required. – Moe Sisko Jan 14 '16 at 00:15
  • 1
    @Hogan, wouldn't you agree that instead of quickly closing this question, you should have at least read it, and even if the title isn't 100% matching with the question, you could have just edited. This would have saved us all time, Milan would have probably got his answer by now, and Stackoverflow as a website would have fulfilled its purpose. – Alex Jan 14 '16 at 02:20
  • @AlexanderMP - I'm still not convinced the answer isn't in the linked question that is why I look forward to another question that shows those techniques failing or shows why they can't be used. – Hogan Jan 14 '16 at 15:59
  • @All: Thank you for the attention. My dev. requirements are quite dynamic so I already had to come up with a workaround. Unfortunately I did not figure out how to use IF COLUM EXISTS within an IN-LINE BCP statement i.e.:bcp "SELECT [col1], [col2], CASE WHEN (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS..." queryout c:\dblog.txt -n -S127.0.0.1,%1 -Uuser -Ppass. I do not think it is possible. Thank you all! – Milan Jan 14 '16 at 18:28

0 Answers0