comma separated values to stored procedures

[shark]

Hi All,

i hv created a sp as
Create proc P @iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR( 100),iClientid) in(
@iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @iclientid as @iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string???

Thanks in Advance.

[Robert Klemme]

shark wrote:

Hi All,

i hv created a sp as
Create proc P @iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR( 100),iClientid) in(
@iclientid)

This does not work as you probably want. You get a selection criterium
with a single string @iClientid. You could have written

select * from clients where CONVERT(VACHAR( 100),iClientid) = @iclientid

end

where iclientid = int data type in the clients table.
now if i pass @iclientid as @iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string???

If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.

robert

[Dan Guzman]

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html

--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xa***********@ gmail.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Hi All,

i hv created a sp as
Create proc P @iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR( 100),iClientid) in(
@iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @iclientid as @iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string???

Thanks in Advance.

[Madhivanan]

Where ','+@iClientid+ ',' like '%,+cast(iClien tid as varchar(20))+', %'

Madhivanan
Dan Guzman wrote:

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html

--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xa***********@ gmail.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Hi All,

i hv created a sp as
Create proc P @iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR( 100),iClientid) in(
@iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @iclientid as @iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string???

Thanks in Advance.