Dynamically assign single quotes

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  August 14, 2009 at 7:46 pm

  #133915

  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

• winash

  SSChampion

  Points: 11446

  More actions

  August 15, 2009 at 4:06 am

  #1039797

  Here's one way to go about this:

  DECLARE @Values varchar(100)

  SET @Values = 'Male,Female,xxx,yyy,z,a,b,c,tt'

  SELECT @Values, REPLACE(QUOTENAME(@Values,''''),',',''',''')

  I'd also recommend http://www.sommarskog.se/arrays-in-sql-2005.html for some very good information on how to handle arrays and lists in SQL Server.

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  August 15, 2009 at 4:48 am

  #1039798

  Thanks Dude!

  It solved my problem.