CODEWITHSUNDEEP
sqlsql-serverstored-procedures
SBB
SBB

Reputation: 8990

TSQL WHERE clause dependent on variable datapoint

I have a query that gives some one the ability to search records by selecting a data type (color, speed) and then depending on the datapoint selected, they can provide criteria to search by.

I am stuck trying to figure out how to change the column that the WHERE clause needs to search by depending on the datapoint selected.

Here some sample code I was trying:

DECLARE @dataType VARCHAR(10);
DECLARE @temp TABLE (color VARCHAR(20), speed VARCHAR(20));
DECLARE @data XML;

-- Insert Temp Data
INSERT INTO @temp( color, speed ) VALUES  ( 'Red', 'Fast' )
INSERT INTO @temp( color, speed ) VALUES  ( 'Blue', 'Slow' )
INSERT INTO @temp( color, speed ) VALUES  ( 'Red', 'Snail' )

-- Test 1
SET @dataType = 'color';
SET @data = '<dataPoints><data><value>Red</value></data></dataPoints>'

-- Test 2
--SET @dataType = 'speed';
--SET @data = '<dataPoints><data><value>Fast</value></data></dataPoints>'

SELECT  color,
        speed 
FROM    @temp AS t
WHERE   
    CASE 
        WHEN 
            @dataType = 'color' 
        THEN 
            t.color IN (SELECT ParamValues.x1.value('value[1]', 'VARCHAR(60)') FROM   @data.nodes('/dataPoints/data') AS ParamValues(x1))
        WHEN
            @dataType = 'speed'
        THEN 
            t.speed IN (SELECT ParamValues.x1.value('value[1]', 'VARCHAR(60)') FROM   @data.nodes('/dataPoints/data') AS ParamValues(x1)) 
    END

My thought process here was that depending on the datapoint selected, the CASE WHEN would tell it to look at a specific column. I don't think this is valid though as my editor is complaining of the syntax.

What would be a better way to handle this?

Upvotes: 0

Views: 73

Answers (2)

DejanS
DejanS

Reputation: 116

You can use dynamically assembled queries. Something like:

DECLARE @dataType VARCHAR(10);
create TABLE #t (color VARCHAR(20), speed VARCHAR(20));
DECLARE @data XML;
declare @value VARCHAR(20)
declare @sql VARCHAR(max)

-- Insert Temp Data
INSERT INTO #t( color, speed ) VALUES  ( 'Red', 'Fast' )
INSERT INTO #t( color, speed ) VALUES  ( 'Blue', 'Slow' )
INSERT INTO #t( color, speed ) VALUES  ( 'Red', 'Snail' )

---- Test 1
--SET @dataType = 'color';
--SET @data = '<dataPoints><data><value>Red</value></data></dataPoints>'

-- Test 2
SET @dataType = 'speed';
SET @data = '<dataPoints><data><value>Fast</value></data></dataPoints>'

SELECT  @value = ParamValues.x1.value('value[1]', 'VARCHAR(60)') FROM   @data.nodes('/dataPoints/data') AS ParamValues(x1)

set @sql = 'SELECT  color,
        speed 
FROM    #t AS t
WHERE   ' + case  @dataType when 'color' then ' t.color ' 
                            when 'speed' then ' t.speed ' else '' end 
+' in (''' +@value  + ''')'

print @sql
exec(@sql)

drop table #t

Upvotes: 0

BJones
BJones

Reputation: 2460

You could try something like this: 

WHERE (@dataType = 'color' AND t.color IN (SELECT ParamValues.x1.value('value[1]', 'VARCHAR(60)') FROM   @data.nodes('/dataPoints/data') AS ParamValues(x1)))
    OR(@dataType = 'speed' AND t.speed IN (SELECT ParamValues.x1.value('value[1]', 'VARCHAR(60)') FROM   @data.nodes('/dataPoints/data') AS ParamValues(x1)))

Upvotes: 2

Related Questions