2

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
S M
  • 159
  • 2
  • 13
  • Please provide sample data, desired results, and an explanation of the logic you want to use. An example of your dynamic query would also help. – Gordon Linoff Sep 27 '18 at 14:10
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Sep 27 '18 at 14:12
  • @GordonLinoff updated the above question with query sample. Thanks – S M Sep 27 '18 at 14:14
  • 2
    This is a presentation layer issue, not a data layer issue. In my rarely-humble opinion, having queries that return different meta-data depending on the parameters used is A Bad Idea. Yes, I realize I may be in the minority on that point. – Brian Sep 27 '18 at 14:53
  • @Brian I will join you on the `Querying Dynamic Metadata Is A Bad Idea` bandwagon. – Shawn Sep 27 '18 at 18:48

2 Answers2

4

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'
Zhorov
  • 28,486
  • 6
  • 27
  • 52
0

We can able to get the columns based on condition. For this we need CASE.

Here, I have added a sample code,

SELECT
    CASE WHEN @param=1 THEN UserFullName ELSE firstName END as userName
FROM users

Please take it.

jawahar N
  • 462
  • 2
  • 13
  • what is the purpose of `CASE WHEN 1=1` ? – Squirrel Sep 27 '18 at 14:27
  • @Squirrel Make sense? :) – jawahar N Sep 27 '18 at 14:32
  • i have no idea. I guess nobody knows what OP wants – Squirrel Sep 27 '18 at 14:33
  • @jawahar what if I do not want to display the column at all if condition does not match. The above query you mention will display column 'UserName' all the time but only the value will be different in the column. But in my case value does not change, just the options to whether select column or no selection at all. – S M Sep 27 '18 at 14:36