We help IT Professionals succeed at work.

SQL search for a value contained within a parameter

woodwyn
woodwyn asked
on
We are creating an interface with multiple checkboxes for categories (Category designation samples: L1,L2,C1,etc).  We will use this interface in multiple environments, each with unique category designations.  I want to create a stored procedure I can use in all environments that will accept a list of the checked category designations, seperated perhaps with a semi-colon, in one character string ('L1;C2;P3'), parse out the category designations and then only include results for those categories.  What is the best way to do this?

CREATE PROCEDURE [GetItems]
@cCategoryCodes char(100)  -- Codes will be seperated by a semi-colon
SELECT
   Items.Number,
   Items.Description,
   ItemCode = (SELECT ItemCategories.Code FROM ItemCategories WHERE ItemCategories.keyItemCategories = Items.keyItemCategories)

FROM Items
WHERE ItemCode IN @cCategoryCodes
Comment
Watch Question

Commented:
CREATE PROCEDURE [GetItems]
@cCategoryCodes char(100)  -- Codes will be seperated by a colon
AS
DECLARE @sql VARCHAR(1000);
SET @sql = 'SELECT Items.Number, Items.Description, ItemCategories.Code '+
  'FROM Items '+
    'INNER JOIN ItemCategories ONItemCategories.keyItemCategories = Items.keyItemCategories '+
    'WHERE ItemCode IN (' + @cCategoryCodes +')'
EXEC  (@sql );

Commented:
if you will use dynamic sql as in my above post you should replace the semi colons with the comma
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Explore More ContentExplore courses, solutions, and other research materials related to this topic.