Convert Dynamically Generated Parameters List Into Stored Proc

I have the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):


if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId
if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId


SELECT
[tblC].[id] AS CombinationID,
[tblC].[availability],
[tblC].[description],
[tblC].[price] AS combinationPrice,
[tblC].[combination_open],
[tblT].[TicketID] AS TicketID,
[tblT].[price] AS ticketPrice,
[tblT].[availability],
[tblT].[ticket_open],
[tblT].[quantity],
[tblT].[event_name],
[tblT].[event_open],
[tblT].[stand_name],
[tblT].[stand_open],
[tblT].[admission_start_date],
[tblT].[admission_end_date],
[tblT].[date_open],
[tblT].,
[tblT].,
[tblT2].[description],
[tblT2].[admin_description]
FROM(
SELECT
[tblCombinations].[id],
[tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
FROM [tblCombinations]
LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
[B]AND [tblEvents].[id]=2
[B]AND [tblStands].[id]=3
--AND [tblAdmissionDates].[id]=@admissionDateId
--AND [tblBookingDates].[id]=@bookingDateId
--AND [tblTicketConcessions].[id]=@concessionId
--AND [tblBookingMinQuantities].[id]=@bookingMinQuantityId
--AND [tblBookingMaxQuantities].[id]=@bookingMaxQuantityId
--AND [tblMemberships].[id]=@membershipId
GROUP BY
[tblCombinations].[id],
[tblTickets].[id],
[tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
) as [tblT]
JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]


I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.

If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.

And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?

:confused: