CODEWITHSUNDEEP
sql-server
user1030181
user1030181

Reputation: 2015

How to convert this dynamic query to normal sql query

How to convert this dynamic query to normal sql query

    SET @SQL = 'SELECT  DelayId, DelayCategory, DelayReason FROM [TAMS].[DelayMaintenance] WHERE IsUtilized = 1 AND ' + @CHECKPOINTTYPE + ' = ' + CAST(1 AS CHAR(1)) + 
       ' union      SELECT 0, NULL, ''--- Pick One----'' ORDER BY DelayId'

EXEC (@SQL)

Upvotes: 0

Views: 501

Answers (2)

user1030181
user1030181

Reputation: 2015

I have tried this 

SELECT  DelayId, DelayCategory, DelayReason
    FROM [TAMS].[DelayMaintenance]
    WHERE CASE @CHECKPOINTTYPE
        WHEN 'TRUCK' THEN Truck
        WHEN 'RAIL' THEN RAIL
        WHEN 'BARGE' THEN BARGE
        ELSE NULL
      END = 1
      AND IsUtilized = 1
    union      SELECT 0, NULL, '--- Pick One----' ORDER BY DelayId

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

Assuming there are a limited number of potential columns names, you can do:

SELECT *
from MyTable
where CASE @CHECKPOINTTYPE
    WHEN 'Column1' THEN Column1
    WHEN 'Column2' THEN Column2
    ELSE NULL
  END = 1

Otherwise you could build the query in a server-side language before passing to SQL Server, which is pretty much the same solution as you are already using.

Upvotes: 1

Related Questions