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]

Re: comma separated values to stored procedures

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]

Re: comma separated values to stored procedures

See Erland's article on the subject:

http://www.sommarskog.se/arrays-in-sql.html

--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xavier.sharon@ gmail.comwrote in message
news:1152022816 .209445.326230@ 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]

Re: comma separated values to stored procedures


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" <xavier.sharon@ gmail.comwrote in message
news:1152022816 .209445.326230@ 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.