using variable values in where part of Select statement

Question

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?

gbn · Accepted Answer · 2011-08-16 14:52:08Z

2

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...

answered Aug 16, 2011 at 14:52

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Safran Ali Over a year ago

i m doing this bcz of this stackoverflow.com/questions/7076628/…, now bcz of performance I am removing all extra things, and you can see in the post that I have created a view that only have distinct Ids and then doing innerjoin with it ... so want customize this query and improve its performance

gbn Over a year ago

@dvlpr: 1. the question here isn't correct then 2. You accepted an answer on the last question: why? 3. What is the point of this question. And "bcz" is not in the English language

Safran Ali Over a year ago

I accepted it because [sorry for bcz] in that question I ask about increasing performance of the query i posted, and the answer I accepted it increased the performance and I have indicated which part of the query has been taking less time, now I am trying to customize (more the 2nd part of the query by breaking it into the parts) myself ... so when I came across using variable in where clause I asked this question ... and I am just trying to make it better, help me if you can in here or in my last question

Sam DeHaan · Accepted Answer · 2011-08-16 14:51:48Z

1

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.

answered Aug 16, 2011 at 14:51

Sam DeHaan

10.4k2 gold badges42 silver badges48 bronze badges

Collectives™ on Stack Overflow

using variable values in where part of Select statement

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related