I want to have a select that will not have fixed number of columns.
OptionalColumn should be selected only if variable @check = 1
This is what I had (syntax is bad but maybe it explains my problem)
(IF @Check = 1
BEGIN OptionalColumn END)
It is crucial to have it only if @Check = 1 and not having it if @Check = 0.
Is it doable?
You could do this with an IF ELSE block like this;
CREATE TABLE #table (Column1 varchar(10), Column2 varchar(10)) INSERT INTO #table VALUES ('column1', 'column2') DECLARE @Check bit; SET @Check = 0 IF @Check = 1 (SELECT Column1, Column2 FROM #table) ELSE (SELECT Column1 FROM #table)
Change the variable from 0 to 1 for testing to see the change in number of columns.
Rich Benner gave you a good solution, as an alternative you can use dynamic sql like that :
DECLARE @sqlvar VARCHAR(100) select @Sqlvar= ' SELECT Collumn1, Collumn2 '+IIF (@check=1,',OptionalCollumn','')+' from TABLE'; EXECUTE sp_executesql @sqlvar
For reference on sp_ executesql look: [https://msdn.microsoft.com/it-it/library/ms188001(v=sql.120).aspx]
Can use IF ELSE block. Please try this.
--Creating a Table. CREATE TABLE #MyTable ( Column1 VARCHAR(10), Column2 VARCHAR(10), OptionalColumn VARCHAR(10) ) --Inserting value to the Table. INSERT INTO #MyTable VALUES('Value 1', 'Value 1','Optional Value') --IF ELSE Logic to desired output. DECLARE @Check bit SET @Check = 0 IF @Check = 1 ( SELECT Column1, Column2 FROM #MyTable ) ELSE ( SELECT Column1, Column2, OptionalColumn FROM #MyTable )