sqlsql-serverdatabasedynamicquerydynamic-queries

Dynamic query to select column based on the condition

Is it possible to select column based on the condition for dynamic queries. If condition is false, then it should not select that column. I want to write below as a dynamic query.

DECLARE @param varchar(10),
SELECT A, 
IF (@param = 'U')
B = A-2, '
From Table tb
Solution

  • You may start with this:

    -- Declarations
DECLARE 
    @param varchar(10),
    @stm nvarchar(max),
    @err int

-- Parameter value
SET @param = 'U' -- or another value

-- Statement
SET @stm = N'SELECT ColumnA'
IF (@param = 'U') SET @stm = @stm + N', ColumnB'
SET @stm = @stm + N' FROM YourTable'

-- Execution
EXEC @err = sp_executesql @stm
IF @err = 0 PRINT 'OK'
ELSE PRINT 'Error'