tsql stored procedure - passing multiple variables

[Guest]

Hi,

I have a dropdown field on a form that allows for multiple selects. When I process the form, I currently use CF to dynamically generate the sql WHERE clause creating as many AND statements as needed.

I'm migrating to MS SQL 2005 and Stored Procedures on the db. I'm trying to create a stored procedure that effectively uses tsql to do what CF was doing previously (creating the WHERE clause).

In the code below, I am passing a char(22) variable called Channel. The code below was my initial attempt at trying to create the WHERE clause. Most of the code is focused on extracting out the individual "Channel" entities. Any thoughts on how to pull this together would be GREATLY appreciated!

I'm need help on the SELECT side, ultimately I'm selecting "regions", from "Syndicated", the WHERE is based on the "Channels" passed.

Thanks,

cfwild

[Guest · Accepted solution]

Hi,

This was quite the adventure trying to figure this out. I learned quite a bit about tsql. Here is the final procedure:

CREATE PROCEDURE dbo.uspSelectSyndicatedRegion
@TablePrefix char(7),
@Category varchar(100),
@Channel varchar(2000)

AS
BEGIN

SELECT DISTINCT S.Region
FROM Syndicated S
JOIN iter_charlist_to_tbl(@Channel, DEFAULT) M
ON S.Channel = M.str
UNION
SELECT ''
ORDER BY S.Region

END