Dynamically assign single quotes

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 2005 Forums

Transact-SQL (2005)

Dynamically assign single quotes

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-14 : 21:49:30

Hi Friends,

I have small requirement.

declare @p_gender varchar(100)
declare @sql varchar(200)
set @p_gender = 'Male'

set @sql = 'select c1,c2,c3 from tname where gender in ('+@p_gender+')'
print @sql

--generated output look like below
select c1,c2,c3 from tname where gender in (Male)

But i need it in below format
select c1,c2,c3 from tname where gender in ('Male')

For this, i have done as follows and my query is running fine.

declare @p_gender varchar(100)
declare @sql varchar(200)
set @p_gender = 'Male'
set @p_gender = ''''+@p_gender+''''
set @sql = 'select c1,c2,c3 from tname where gender in ('+@p_gender+')'
print @sql

Case-2

suppose user might select both Male and Female from FrontEnd and to my stored procedure,
i will get the gender string as follows
set @p_gender = 'Male,Female'

Case-3

This user might select Male,Female,Other also , then how to set my single quotes for all the values correctly and dynamically?
set @p_gender = 'Male,Female,Other'

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 21:55:19

http://www.sommarskog.se/arrays-in-sql-2005.html
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

KH
[spoiler]Time is always against us[/spoiler]

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-14 : 22:21:45

Hi,

The two links are very good but my question is different.

I want append the ''single quotes to the @p_gender variables based on the selection dynamically.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 22:40:49

you don't need dynamic SQL at all

select c1,c2,c3 from tname where gender in (select stringval from CSVTable(@p_gender))

KH
[spoiler]Time is always against us[/spoiler]

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-15 : 00:30:54

Thank you.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-15 : 00:36:15

Hey, the solution was too good.

It is working for both numeric and string parameters.

But i am confused what is written inside the Function.

Need to investigate on it!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:34:08

quote:
Originally posted by frank.svs

Hey, the solution was too good.

It is working for both numeric and string parameters.

But i am confused what is written inside the Function.

Need to investigate on it!!!

the function does nothing more than parsing the csv list and giving back a table of values

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-15 : 09:02:28

quote:
Originally posted by frank.svs

Hey, the solution was too good.

It is working for both numeric and string parameters.

But i am confused what is written inside the Function.

Need to investigate on it!!!

use numberval instead of stringval if your value is numeric

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources