I have read all the articles regarding this issue.
I would like to implent an "select all" function in the multivalued prompt.
The multivalued datavalues comes from a table. e.x.
select label,value from basetable
union
select 'All' as label,'%' as value
The user choice comes in the parameter @.BasePick
I have tried different SQL
1:
select id,data1,data2 from Trans t
inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
result: SQL gives an "comma error" when user pick several multivalue elements
2:
select id,data1,data2 from Trans t
where @.BasePick='%' or t.id in (@.BasePick)
result: SQL gives an "comma error" when user pick several multivalue elements
I guess that there are no way to convert an RS array to a string for use in
SQL. What is the best pracsis here.
--
Regards
Henrik JuelI presume you use RS2005.
the SP2 will provide the Select All option in a multivalue parameter.
to convert a multivalue to a string, use the Join code:
=Join(myparam.Value, ",")
returns a comma separated string.
But in your case, use 2 parameters in your query.
The first one use the BasePick multivalue parameter
the second one will use a formula (RS formula) like:
select id,data1,data2 from Trans t
where @.AllPick = '%' or t.id in (@.BasePick)
@.AllPick will receive the result of this formula:
=myparam.Value(0)
(to return only the first value from the multivalue)
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>I have read all the articles regarding this issue.
> I would like to implent an "select all" function in the multivalued
> prompt.
> The multivalued datavalues comes from a table. e.x.
> select label,value from basetable
> union
> select 'All' as label,'%' as value
> The user choice comes in the parameter @.BasePick
> I have tried different SQL
> 1:
> select id,data1,data2 from Trans t
> inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> 2:
> select id,data1,data2 from Trans t
> where @.BasePick='%' or t.id in (@.BasePick)
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> I guess that there are no way to convert an RS array to a string for use
> in
> SQL. What is the best pracsis here.
> --
> Regards
> Henrik Juel|||Hi all,
I having same problem with these multi value params. I am using that
"CharList" function in my SSRS 2005 report SP s.I have number of
Cascading multi value parameters in my reprots. And also these mv
params taking long time to refresh. If user selects 'All' giving an
"comma error". Can any body tell me which one is the better methodology
in performance wise. If you people solved this problem please let me
know..
Thanks,
Munny.
Jeje wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel|||Thx for a good reply.
Especially using two parameters was useful. I use an interal RS variable
@.Allpick that default is set to BasePick.value(0). Then I can use the
where @.AllPick = '%' or t.id in (@.BasePick)
Regards
Henrik Juel
"Jeje" wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel
>|||in fact, an internal variable is not required.
you can just map the @.AllPick query parameter to a formula instead-of
creating a new report parameter.
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:D42F6BDE-08C5-466F-9D79-6B9973B62E56@.microsoft.com...
> Thx for a good reply.
> Especially using two parameters was useful. I use an interal RS variable
> @.Allpick that default is set to BasePick.value(0). Then I can use the
> where @.AllPick = '%' or t.id in (@.BasePick)
>
> --
> Regards
> Henrik Juel
>
> "Jeje" wrote:
>> I presume you use RS2005.
>> the SP2 will provide the Select All option in a multivalue parameter.
>> to convert a multivalue to a string, use the Join code:
>> =Join(myparam.Value, ",")
>> returns a comma separated string.
>> But in your case, use 2 parameters in your query.
>> The first one use the BasePick multivalue parameter
>> the second one will use a formula (RS formula) like:
>> select id,data1,data2 from Trans t
>> where @.AllPick = '%' or t.id in (@.BasePick)
>> @.AllPick will receive the result of this formula:
>> =myparam.Value(0)
>> (to return only the first value from the multivalue)
>>
>> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
>> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>> >I have read all the articles regarding this issue.
>> >
>> > I would like to implent an "select all" function in the multivalued
>> > prompt.
>> > The multivalued datavalues comes from a table. e.x.
>> >
>> > select label,value from basetable
>> > union
>> > select 'All' as label,'%' as value
>> >
>> > The user choice comes in the parameter @.BasePick
>> >
>> > I have tried different SQL
>> >
>> > 1:
>> > select id,data1,data2 from Trans t
>> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > 2:
>> > select id,data1,data2 from Trans t
>> > where @.BasePick='%' or t.id in (@.BasePick)
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > I guess that there are no way to convert an RS array to a string for
>> > use
>> > in
>> > SQL. What is the best pracsis here.
>> >
>> > --
>> > Regards
>> > Henrik Juel
Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
Convert int to month name?
Is there an easy way of doing this?
I have a report where the user selects the month and year as parameters
passed in. I then want to show these on the report, but display the month as
month name rather than ordinal.
I tried using the GetMonthName function but it moans about it not
referencing an object.
The only way I can think of is to have a lengthy and unreadable 'switch'
statement.
Is there a better neater way of achieving this?Set up your month parameter as non quried and set values to the number 1- 12
set the labels as Jan-Dec. Whereever you want to display the month use this
expression
Parameters!<ParmeterName>.Label
"adolf garlic" wrote:
> Is there an easy way of doing this?
> I have a report where the user selects the month and year as parameters
> passed in. I then want to show these on the report, but display the month as
> month name rather than ordinal.
> I tried using the GetMonthName function but it moans about it not
> referencing an object.
> The only way I can think of is to have a lengthy and unreadable 'switch'
> statement.
> Is there a better neater way of achieving this?|||I suspect that your data contains a date value.
In that case you could use it in stead of the parameter value:
Format(first(Fields!dateColumn.value), "MMMM")
My 2c.
Flip
"adolf garlic" wrote:
> Is there an easy way of doing this?
> I have a report where the user selects the month and year as parameters
> passed in. I then want to show these on the report, but display the month as
> month name rather than ordinal.
> I tried using the GetMonthName function but it moans about it not
> referencing an object.
> The only way I can think of is to have a lengthy and unreadable 'switch'
> statement.
> Is there a better neater way of achieving this?|||works a treat
ta
"johnE" wrote:
> Set up your month parameter as non quried and set values to the number 1- 12
> set the labels as Jan-Dec. Whereever you want to display the month use this
> expression
> Parameters!<ParmeterName>.Label
>
> "adolf garlic" wrote:
> > Is there an easy way of doing this?
> >
> > I have a report where the user selects the month and year as parameters
> > passed in. I then want to show these on the report, but display the month as
> > month name rather than ordinal.
> >
> > I tried using the GetMonthName function but it moans about it not
> > referencing an object.
> >
> > The only way I can think of is to have a lengthy and unreadable 'switch'
> > statement.
> >
> > Is there a better neater way of achieving this?|||Nope this just gives the lyrics to a crash test dummies song
namely MMMM as the result
johnE's suggestion worked though
"Flip" wrote:
> I suspect that your data contains a date value.
> In that case you could use it in stead of the parameter value:
> Format(first(Fields!dateColumn.value), "MMMM")
> My 2c.
> Flip
>
> "adolf garlic" wrote:
> > Is there an easy way of doing this?
> >
> > I have a report where the user selects the month and year as parameters
> > passed in. I then want to show these on the report, but display the month as
> > month name rather than ordinal.
> >
> > I tried using the GetMonthName function but it moans about it not
> > referencing an object.
> >
> > The only way I can think of is to have a lengthy and unreadable 'switch'
> > statement.
> >
> > Is there a better neater way of achieving this?|||I got the same result but figured it out. My query is passing just the month
number ( DATEPART(MM,DateField) ). So True argumen would error out. I changed
my expression to :
=Left(MONTHNAME(Fields!Month.Value),3)
So now it shows Jan, Feb, Mar, etc. rather than 1,2,3,etc.
Great tip, John E.
"adolf garlic" wrote:
> Nope this just gives the lyrics to a crash test dummies song
> namely MMMM as the result
> johnE's suggestion worked though
>
> "Flip" wrote:
> > I suspect that your data contains a date value.
> >
> > In that case you could use it in stead of the parameter value:
> >
> > Format(first(Fields!dateColumn.value), "MMMM")
> >
> > My 2c.
> > Flip
> >
> >
> > "adolf garlic" wrote:
> >
> > > Is there an easy way of doing this?
> > >
> > > I have a report where the user selects the month and year as parameters
> > > passed in. I then want to show these on the report, but display the month as
> > > month name rather than ordinal.
> > >
> > > I tried using the GetMonthName function but it moans about it not
> > > referencing an object.
> > >
> > > The only way I can think of is to have a lengthy and unreadable 'switch'
> > > statement.
> > >
> > > Is there a better neater way of achieving this?
I have a report where the user selects the month and year as parameters
passed in. I then want to show these on the report, but display the month as
month name rather than ordinal.
I tried using the GetMonthName function but it moans about it not
referencing an object.
The only way I can think of is to have a lengthy and unreadable 'switch'
statement.
Is there a better neater way of achieving this?Set up your month parameter as non quried and set values to the number 1- 12
set the labels as Jan-Dec. Whereever you want to display the month use this
expression
Parameters!<ParmeterName>.Label
"adolf garlic" wrote:
> Is there an easy way of doing this?
> I have a report where the user selects the month and year as parameters
> passed in. I then want to show these on the report, but display the month as
> month name rather than ordinal.
> I tried using the GetMonthName function but it moans about it not
> referencing an object.
> The only way I can think of is to have a lengthy and unreadable 'switch'
> statement.
> Is there a better neater way of achieving this?|||I suspect that your data contains a date value.
In that case you could use it in stead of the parameter value:
Format(first(Fields!dateColumn.value), "MMMM")
My 2c.
Flip
"adolf garlic" wrote:
> Is there an easy way of doing this?
> I have a report where the user selects the month and year as parameters
> passed in. I then want to show these on the report, but display the month as
> month name rather than ordinal.
> I tried using the GetMonthName function but it moans about it not
> referencing an object.
> The only way I can think of is to have a lengthy and unreadable 'switch'
> statement.
> Is there a better neater way of achieving this?|||works a treat
ta
"johnE" wrote:
> Set up your month parameter as non quried and set values to the number 1- 12
> set the labels as Jan-Dec. Whereever you want to display the month use this
> expression
> Parameters!<ParmeterName>.Label
>
> "adolf garlic" wrote:
> > Is there an easy way of doing this?
> >
> > I have a report where the user selects the month and year as parameters
> > passed in. I then want to show these on the report, but display the month as
> > month name rather than ordinal.
> >
> > I tried using the GetMonthName function but it moans about it not
> > referencing an object.
> >
> > The only way I can think of is to have a lengthy and unreadable 'switch'
> > statement.
> >
> > Is there a better neater way of achieving this?|||Nope this just gives the lyrics to a crash test dummies song
namely MMMM as the result
johnE's suggestion worked though
"Flip" wrote:
> I suspect that your data contains a date value.
> In that case you could use it in stead of the parameter value:
> Format(first(Fields!dateColumn.value), "MMMM")
> My 2c.
> Flip
>
> "adolf garlic" wrote:
> > Is there an easy way of doing this?
> >
> > I have a report where the user selects the month and year as parameters
> > passed in. I then want to show these on the report, but display the month as
> > month name rather than ordinal.
> >
> > I tried using the GetMonthName function but it moans about it not
> > referencing an object.
> >
> > The only way I can think of is to have a lengthy and unreadable 'switch'
> > statement.
> >
> > Is there a better neater way of achieving this?|||I got the same result but figured it out. My query is passing just the month
number ( DATEPART(MM,DateField) ). So True argumen would error out. I changed
my expression to :
=Left(MONTHNAME(Fields!Month.Value),3)
So now it shows Jan, Feb, Mar, etc. rather than 1,2,3,etc.
Great tip, John E.
"adolf garlic" wrote:
> Nope this just gives the lyrics to a crash test dummies song
> namely MMMM as the result
> johnE's suggestion worked though
>
> "Flip" wrote:
> > I suspect that your data contains a date value.
> >
> > In that case you could use it in stead of the parameter value:
> >
> > Format(first(Fields!dateColumn.value), "MMMM")
> >
> > My 2c.
> > Flip
> >
> >
> > "adolf garlic" wrote:
> >
> > > Is there an easy way of doing this?
> > >
> > > I have a report where the user selects the month and year as parameters
> > > passed in. I then want to show these on the report, but display the month as
> > > month name rather than ordinal.
> > >
> > > I tried using the GetMonthName function but it moans about it not
> > > referencing an object.
> > >
> > > The only way I can think of is to have a lengthy and unreadable 'switch'
> > > statement.
> > >
> > > Is there a better neater way of achieving this?
Sunday, February 12, 2012
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: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.
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.
Subscribe to:
Posts (Atom)