Variables with WHERE IN (@VariableName) (can't get to work)

Last Post 17 Oct 2007 05:27 PM by tom27. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Maxer
New Member
New Member

--
17 Oct 2007 07:40 AM
I know I'm doing something foolish here and I'm just not seeing it.

I have (for example)

SELECT StateID, StateName
FROM tblStates
WHERE StateID IN (1, 2, 3)

This works just fine.

So I want to use a variable now and get:


DECLARE @varStateID AS varchar
SET @varStateID = '1, 2, 3'

SELECT StateID, StateName
FROM tblStates
WHERE StateID IN (@varStateID)

However when I run this, I get no results.

So I assume I am not using my variable correctly with the list.

What am I doing wrong?
Maxer
New Member
New Member

--
17 Oct 2007 10:05 AM
So then how does Reporting Services resolve that?

Is it using an array when it generates the SQL?

In Reporting Services I can use that same TSQL in a query design and it takes care of it.

(Also thanks for the link, I'll read those)
tom27
New Member
New Member

--
17 Oct 2007 05:27 PM
I have a question here, Instead of we writing a loop or maintaining a number table can we use the XML capabilities of Sqlserver to solve this problem. How bad will be that?
You are not authorized to post a reply.

Acceptable Use Policy