Select with "IN" clause

Question

Select with "IN" clause

daquere

Valued Member

Points: 73
More actions
May 8, 2011 at 9:26 am

#145506

Hi everyone,
Please, some light over my problem...
I´m executing the query:
declare @dir varchar
SET @dir = '4, 2'
SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))
The problem is that only the first record is showed "4", if I change to '2, 4' only one record is showed, too, "2".
Thanks in advance. Best
Daniel

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

IN does not work with a variable. You either need to use dynamic SQL (and risk SQL Injection) or find a split function and use IN with a subquery.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

daquere Valued Member Points: 73 More actions · Answer 2

Hi Gila,

Please, do you have an example to do this or where I can found the way. I´m very new with database query.

Thanks in advance. Best

Daniel

WayneS SSC Guru Points: 95460 More actions · Answer 3

Look up sp_executesql in Books-Online

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

Just in case you're REALLY new... the "Books Online" Wayne speaks of is the Help system that comes with SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

WayneS (5/8/2011)
Look up sp_executesql in Books-Online

Or search for a split function on this website. Safer than dynamic SQL (because of SQL Injection and the permission requirements of dynamic SQL)

I don't have a link offhand, but I'm pretty sure someone will come along shortly and post one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pkaursikhni Grasshopper Points: 15 More actions · Answer 6

declare @dir varchar

SET @dir = '4, 2'

SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))

change and try as follows :

''Note : you have to remove quote (') if it is numeric values

declare @dir varchar

SET @dir = 4, 2

SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))

Note : You have to add quote(') if it is varchar value as follows:

declare @dir varchar

SET @dir = '4','2'

SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

pkaursikhni (5/9/2011)
''Note : you have to remove quote (') if it is numeric values
declare @dir varchar
SET @dir = 4, 2
SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))
Note : You have to add quote(') if it is varchar value as follows:
declare @dir varchar
SET @dir = '4','2'
SELECT *, empresa FROM DIRETORIAS WHERE (empresa IN (@dir))

Both of those are going to return an error,

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ','.

SQL doesn't have arrays and you can't assign two or more values to a variable.

p.s. Never rely on the default length of a data type. Do you know what length a varchar "declare @dir varchar" gives you?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

daquere Valued Member Points: 73 More actions · Answer 8

Thanks of all for suggestions.

The type of column "empresa" is integer, if the column type was varchar, would be better?

The information "@dir" comes from asp.net like a varchar.

I´m reading about split function and sp_executesql.

pkaursikhni, I´m getting error, as well. tks

Best,

Daniel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

daquere (5/9/2011)
he type of column "empresa" is integer, if the column type was varchar, would be better?

No. If it's numeric data then it should be an integer data type. Changing to string is not going to make the solution any easier and may have later side effects

pkaursikhni, I´m getting error, as well. tks

Because his code is trying to do something that SQL cannot and will not do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nigel. SSChampion Points: 11636 More actions · Answer 10

Have a look at this:

http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

understand it well, then this:

http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

Nigel
How to post questions

Rich Mechaber SSChampion Points: 11130 More actions · Answer 11

If you define a column as a VARCHAR type without specifying the length, it defaults to a length of 1.

BOL: http://msdn.microsoft.com/en-us/library/ms176089.aspx

Rich