making multiple choice checklist in sql

Question

I am studying Sql server and trying to do different examples. Can you suggest me what to use in this case. Below is my ckecklist(not checkbox, opens in different window as list):

Colors 
   Red
   Blue
  White
  Black
  Green

From table item.colors

Want to provide search according to the choosed records. For example, lets say 'Red' and 'Blue' were choosed and as output want to get associated records from table.

Colors
  Red     v
  Blue    v
  White
  Black 
  Green

so the output:

  id    Name      Colors
  1    T-shirt    Red      
  2    Boots      Blue

Any suggestions how to do this? even if to use parameters then what to do in case of hundreds records? we can not have 100 parameters)

You need to make list from cheked items and put in `IN` statement http://msdn.microsoft.com/en-us/library/ms177682(v=sql.105).aspx — Justin, Mar 28 '13 at 08:32

score 0 · Answer 1 · edited May 23 '17 at 11:57

there are two options you can use (probably more):

A. using String parameter

First, concatenate your values into single string in application and send it as one varhcar parameter. It may look like this: 'Red;Blue' (or probably some IDs). Afterwards SQL procedure should split the string back to list of values and put it the the #temp table. There are plenty of string splitting functions out there on the web. You can easily google for it or check this link for some ideas: How do I split a string so I can access item x? Eventually just join your values from #temp table with your tables to get desired data.

B. XML

This is a bit more 'fancy' option. Put your values from app into XML and pass it to stored procedure as XML parameter. I don't know what programming language you use, but there should be a provided methods to do this. In your procedure then - if you know how - you can directly join the XML parameter with your tables to filter data or, again, you can spill them to #temp table first. Yet again, there are plenty of information out there how to work with XMLs. Some examples: Select values from XML field in SQL Server 2008 http://blog.sqlauthority.com/2010/06/23/sqlauthority-news-guest-post-select-from-xml-jacob-sebastian/

EDIT: (third option, easiest but I wouldn't recommend it)

C. Dynamic SQL

Create concatenated string just like in option A, but inside procedure - do not split it back to values but just glue it to string with SELECT statement and use sp_executesql or EXEC.

SET @sql = 'SELECT * FROM YourTable WHERE Color IN (' + @parameter + ')'
EXEC(@sql)

making multiple choice checklist in sql

1 Answers1