CODEWITHSUNDEEP
sqlsql-serverselectwhere-clause
Safran Ali
Safran Ali

Reputation: 4497

using variable values in where part of Select statement

Here is the code:

declare
    @allcounterids varchar(max),
    @counteridquery varchar(max);

select
    @allcounterids = stuff((
    select 
            '],[' + cast([CounterId] as varchar(32))
        from
            AllCounters
        WHERE DateTime > '2011-08-15' and DateTime < '2011-08-19' and [Type] = 1
        for xml path('')), 1, 2, '') + ']';

Select statement

SELECT [Type], [DateTime], Value, AllCounters.CounterId
            FROM AllCounters
            WHERE CounterId IN @allcounterids

as you can see i have created the varialble '@allcounterids' and populated data in it, my question is can I use this variable in where clause of Select?

Upvotes: 0

Views: 521

Answers (2)

gbn
gbn

Reputation: 432261

No, you can't have a CSV string and use it with the IN filter ("predicate"). SQL doesn't work this way without dynamic SQL: which isn't needed in this case

It can be done in one go, thus

SELECT [Type], [DateTime], Value, AllCounters.CounterId
FROM AllCounters
WHERE CounterId IN
    (select [CounterId]
    from AllCounters
    WHERE DateTime > '2011-08-15' and DateTime < '2011-08-19' and [Type] = 1
    )

But, saying that that, why not just do this?

SELECT [Type], [DateTime], Value, AllCounters.CounterId
FROM AllCounters
WHERE DateTime > '2011-08-15' and DateTime < '2011-08-19' and [Type] = 1

Unless your question is incomplete and lacks information...

Upvotes: 2

Sam DeHaan
Sam DeHaan

Reputation: 10325

I've used this before (DISCLAIMER: I used MS SQL Server, you haven't specified RDBMS), but it only works in Dynamic SQL. Construct a query String, sanitize all of your inputs, and exec.

Upvotes: 1

Related Questions