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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 comma seperated list to stored proc
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwikwisi
Constraint Violating Yak Guru

282 Posts

Posted - 02/09/2010 :  21:41:04  Show Profile  Reply with Quote
Below is where condition and I passed comma seperated list (x) from front end to @test.But It returns null even the value is inside.
dim x as string = "aa,bb,cc"

(col1 IN(' + @test + ') OR @test IS NULL)

Where is it wrong ?

Thanks

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/09/2010 :  21:57:17  Show Profile  Reply with Quote
read this

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql-2005.html


KH
Time is always against us

Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

282 Posts

Posted - 02/09/2010 :  22:10:06  Show Profile  Reply with Quote
I just followed it.

quote:
Originally posted by khtan

read this

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql-2005.html


KH
Time is always against us



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/09/2010 :  22:14:23  Show Profile  Reply with Quote
pls post your full query


KH
Time is always against us

Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 02/09/2010 :  23:04:04  Show Profile  Reply with Quote
Since you are passing a string value and not integer value, i feel that all individual value should be in single quotes.

Just try by adding the below statement to your code before calling it dynamically.

Set @test = '''' + Replace(@test, ',' , ''',''' ) + ''''

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/10/2010 :  00:19:36  Show Profile  Reply with Quote
quote:
Originally posted by kwikwisi

Below is where condition and I passed comma seperated list (x) from front end to @test.But It returns null even the value is inside.
dim x as string = "aa,bb,cc"

(col1 IN(' + @test + ') OR @test IS NULL)

Where is it wrong ?

Thanks


are you asking about sql or vb? sql doesnt have dim.
if sql it should be

',' + @test + ',' LIKE '%,'+ col1 + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

282 Posts

Posted - 02/10/2010 :  04:21:59  Show Profile  Reply with Quote
Yes, I know SQL doesnt have Dim .
I just let you know how I passed the string value from VB.Net.

Thanks.
quote:
Originally posted by visakh16

quote:
Originally posted by kwikwisi

Below is where condition and I passed comma seperated list (x) from front end to @test.But It returns null even the value is inside.
dim x as string = "aa,bb,cc"

(col1 IN(' + @test + ') OR @test IS NULL)

Where is it wrong ?

Thanks


are you asking about sql or vb? sql doesnt have dim.
if sql it should be

',' + @test + ',' LIKE '%,'+ col1 + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/10/2010 :  04:25:11  Show Profile  Reply with Quote
ok and does my suggestion work in sql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000