|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > Passing parameter to stored procedure with embedded single quote
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Passing parameter to stored procedure with embedded single quote
|
|
|
| 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')
| |
| John Fichera 2002-12-20, 3:23 pm |
| 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')
>
| |
|
| 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')
>>
>
>
>
|
|
|
|
|