Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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 but a very different question):

    Code:
    if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId
    if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId
    Code:
    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].[booking_start_date],
    [tblT].[booking_end_date],
    [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
    	AND [tblEvents].[id]=2
    	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?


  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    A common method is:
    Code:
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Well, seeing as my state of blissful ignorance safely censored your "optimiser" comment, I can happily report that the solution works great Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •