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:Hi
A common method is:
WHERE (MyField = @.MyParam OR @.MyParam = 0)
I read an article somewhere though that poohed poohed this as the optimiser can't use the index or something though.|||Well, seeing as my state of blissful ignorance safely censored your "optimiser" comment, I can happily report that the solution works great :) Thanks.
