single quotes as a variable pass to a sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

single quotes as a variable pass to a sp

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-03-16 : 21:49:01

hi guys, i need your help on this.

I created a very simple sp that only has a variable to pass in

sth like this

CREATE PROCEDURE testing
@Variable varchar(100)
as
SELECT * from TableA
WHERE fieldA in (@Variable)
GO

I want to exec the sp with passing several items in the @variable like this

exec testing @Variable = 'A', 'B', 'C' (of course this is incorrect)

exactly, how can i do this so i can

select * from TableA where fieldA in ('A', 'B', 'C') ?

any tips is very welcome.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-17 : 02:04:22

Try this:
alter PROCEDURE testing
@Variable varchar(100)
as
print('SELECT * from TableA WHERE fieldA in ('+@Variable+')')
exec ('SELECT * from TableA WHERE fieldA in ('+@Variable+')')
GO

exec testing '''aa'',a'

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-17 : 09:03:58

Look at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24620

and also search the site for CSV (comma-seperated values).

A UDF is exactly what you are looking for; if not, parse the string argument into a temp table or table variable in your stored proc and then do a WHERE .. IN (temptable).

I try to avoid dynamic SQL whenever possible ... it's not a bad thing or anything, i just feel it should be a last resort usually.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources