Problem with IN Clause in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with IN Clause in query

Author

Topic

ruchirj07
Starting Member

37 Posts

Posted - 2007-11-13 : 02:34:14

Hi,

Can't we fire a query with IN clause having variable as input in SQL SERVER stored procedure.
ex.
@var = '1,2,3,4,5,6,7'
select * from table where column in (@var)

I see that the query is always returning no result though if i fire the query directly it gives me the proper result.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-13 : 02:57:50

have you ever used any dynamic sql?

look in BOL or search here, there are lots of examples

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 03:07:54

or

select * from table where ','+@var+',' like '%,'+cast(column as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

ruchirj07
Starting Member

37 Posts

Posted - 2007-11-13 : 05:03:37

Can anyone give me an example for the same?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-13 : 05:09:34

[code]
declare @list varchar(20)
set @list = '1,2,3'

execute( 'select * from yourtable where column in (' + @list + ')' )[/code]

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 05:14:47

And there you lost all your table due to sql injection

E 12°55'05.25"
N 56°04'39.16"

ruchirj07
Starting Member

37 Posts

Posted - 2007-11-13 : 05:14:59

I want the output of the query in the stored procedure itself.
My scenario is i have to check whether the passed variable exists in the table or not. Using dynamic sql how will i get the output?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-13 : 05:17:45

quote:

Peso Posted - 11/13/2007 : 05:14:47
--------------------------------------------------------------------------------
And there you lost all your table due to sql injection

ssshhh.... you spoil all the fun

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 05:30:39

quote:
Originally posted by ruchirj07

I want the output of the query in the stored procedure itself.
My scenario is i have to check whether the passed variable exists in the table or not. Using dynamic sql how will i get the output?

If you want to know existance of the record, then use

IF EXISTS(select.......)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 05:32:32

quote:
Originally posted by ruchirj07

Can anyone give me an example for the same?

What did you think of the suggestion I have posted?
Isnt it an example?

Madhivanan

Failing to plan is Planning to fail

ruchirj07
Starting Member

37 Posts

Posted - 2007-11-13 : 06:40:29

I want to catch the output of the dynamic query generated in stored procedure itself.
If want to know whether this record exists in the table or not than how will i get the o/p from this dynamic query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources