I have this stored procedure:

CREATE PROCEDURE getLoc
@Location varchar(200) = NULL

AS
select * from tblCust where Location IN (@Location)

When I called the stored proc: "exec getLoc @Location='la'", it
works.

If I want to pass multiple location such as 'la', 'sd' and so on,
why would I pass the values since each location needs to be
embedded inside single quote to make the query look like this:

select * from tblCust where Location IN ('la','sd')

Report this post to a moderator

Not sure if this is the best way, but I guess you could do something like
this...

declare @input varchar(255),@final varchar(255)
select @input = 'sysusers,sysobjects'
select @final=char(39)+replace(@input
,',',char(39)+','+char(39))+ch
ar(39)
exec('select name from sysobjects where name in ('+@final+')')


"MLP" <MLP@Hotmail.com> wrote in message
news:l0KM9.27917$3t6.992@nwrddc03.gnilink.net...
> I have this stored procedure:
>
> CREATE PROCEDURE getLoc
> @Location varchar(200) = NULL
>
> AS
> select * from tblCust where Location IN (@Location)
>
> When I called the stored proc: "exec getLoc @Location='la'", it
> works.
>
> If I want to pass multiple location such as 'la', 'sd' and so on,
> why would I pass the values since each location needs to be
> embedded inside single quote to make the query look like this:
>
> select * from tblCust where Location IN ('la','sd')
>

Report this post to a moderator

It is a good idea, but when I created this stored proc:

CREATE PROCEDURE test
@input varchar(255)
AS
declare @final varchar(255)

select
@final=char(39)+replace(@input
,',',char(39)+','+char(39))+ch
ar(39)
select name from sysobjects where name in (@final)

GO

It won't work when I do "exec test @input='sysusers,sysobjects'.
Do you know why? Is there a way to execute a string inside a
stored proc like you did?

MLP

John Fichera wrote:
> Not sure if this is the best way, but I guess you could do something like
> this...
>
> declare @input varchar(255),@final varchar(255)
> select @input = 'sysusers,sysobjects'
> select @final=char(39)+replace(@input
,',',char(39)+','+char(39))+ch
ar(39)
> exec('select name from sysobjects where name in ('+@final+')')
>
>
> "MLP" <MLP@Hotmail.com> wrote in message
> news:l0KM9.27917$3t6.992@nwrddc03.gnilink.net...
>
>>I have this stored procedure:
>>
>>CREATE PROCEDURE getLoc
>> @Location varchar(200) = NULL
>>
>>AS
>> select * from tblCust where Location IN (@Location)
>>
>>When I called the stored proc: "exec getLoc @Location='la'", it
>>works.
>>
>>If I want to pass multiple location such as 'la', 'sd' and so on,
>>why would I pass the values since each location needs to be
>>embedded inside single quote to make the query look like this:
>>
>> select * from tblCust where Location IN ('la','sd')
>>
>
>
>

Report this post to a moderator