SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwikwisi
Constraint Violating Yak Guru

281 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
17681 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

281 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
17681 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
52325 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

281 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
52325 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000