Ignore Parameter 2

[chrissie1]

I have an SP

That goes something like this

CREATE PROCEDURE [dbo].[spo_KitsGeneralStatistic] 
(
	@KitType varchar(36)
)
AS

SELECT     
(	
	SELECT	COUNT(*)
	FROM	[tbl_kit]
	WHERE   [kittype] = @kittype) 
	AS 	[UsedKits])
,(...
)

This works fine if I give it a kittype.

But I would like to use the same SP for my totals. So for every kittype. In essence when kittype IS NULL then ignore it and give everything.

Is this possible? yes.

How? I have no idea, can anybody help?

Christiaan Baes
Belgium

"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[gmmastros]

You're getting an index scan because every row in the table needs to be examined. [!]If[/!] all of the individual counts have kittype as a filter, then you will probably get better performance by moving that filter condition to the where clause.

Like this...

SELECT Sum(Case When kitstatus = @UsedLaboPJ
                     [s][!]And (@kittype IS NULL Or kittype = @kittype)[/!][/s]
                Then 1 Else 0 End) As UsedKits,
       Sum(Case When kitstatus = @NotUsedLaboPJ 
                     [s][!]And (@kittype IS NULL Or kittype = @kittype)[/!][/s]
                Then 1 Else 0 End) As KitsNotReturned,
       Sum(Case When kitstatus = @UsedLaboPj
                     AND labcontact IS NOT NULL
                     [s][!]And (@kittype IS NULL Or kittype = @kittype)[/!][/s]
                Then 1 Else 0 End) As FormsReturned,
       Sum(Case When FactType = @FactType11
                     [s][!]And (@kittype IS NULL Or kittype = @kittype)[/!][/s]
                Then 1 Else 0 End) As NumberOfCP11
,
(SELECT     COUNT(k.KitNumber)
    FROM     tbl_kit k
        JOIN tbl_kit_ObjectsSampled ko ON k.kitnumber = ko.kitnumber
    WHERE     ko.ObjectsSampled_id = @ObjectsSampled
        AND (@kittype IS NULL Or kittype = @kittype)
        AND kitstatus = @UsedLaboPj
        AND labcontact IS NOT NULL
)AS     NumberOfVehicules
FROM   tbl_kit
[!]Where  (@kittype IS NULL Or kittype = @kittype)[/!]

-George

"the screen with the little boxes in the window." - Moron

 

[LNBruno]

George - won't he still get an index scan due to that same filter in the subquery?

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[gmmastros]

I don't think so. One thing I've noticed about my own abilities is that I cannot always predict when an index scan will be used instead of an index seek. I'm getting better at predicting, but I always need to check.

Mike,

Would you change your mind if the query looked like this?

[blue]Declare @NumberOfVehicles Int

SELECT  @NumberOfVehicles = COUNT(k.KitNumber)
FROM    tbl_kit k
        JOIN tbl_kit_ObjectsSampled ko ON k.kitnumber = ko.kitnumber
WHERE   ko.ObjectsSampled_id = @ObjectsSampled
        AND (@kittype IS NULL Or kittype = @kittype)
        AND kitstatus = @UsedLaboPj
        AND labcontact IS NOT NULL
[/blue]

SELECT Sum(Case When kitstatus = @UsedLaboPJ
                     And (@kittype IS NULL Or kittype = @kittype)
                Then 1 Else 0 End) As UsedKits,
       Sum(Case When kitstatus = @NotUsedLaboPJ
                     And (@kittype IS NULL Or kittype = @kittype)
                Then 1 Else 0 End) As KitsNotReturned,
       Sum(Case When kitstatus = @UsedLaboPj
                     AND labcontact IS NOT NULL
                     And (@kittype IS NULL Or kittype = @kittype)
                Then 1 Else 0 End) As FormsReturned,
       Sum(Case When FactType = @FactType11
                     And (@kittype IS NULL Or kittype = @kittype)
                Then 1 Else 0 End) As NumberOfCP11,
       [blue]@NumberOfVehicles As NumberOfVehicules[/blue]
FROM   tbl_kit
Where  (@kittype IS NULL Or kittype = @kittype)

-George

"the screen with the little boxes in the window." - Moron

 

[ca8msm]

What about if you append an index hint e.g.

WITH (INDEX(kittype_index))

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ca8msm]

Actually, I think I read your response wrong - I thought it wasn't using the index at all.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[LNBruno]

One thing I've noticed about my own abilities is that I cannot always predict...

I'd take your predictions over my "knowns" any day. ;-)

And, yeah, I would guess that newer version would prevent the scan.

Man! I LOVE this place!

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[chrissie1]

I'll try that tomorow when the last day of the week starts.

Christiaan Baes
Belgium

"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[chrissie1]

So I have been at it for a while and nothing seems to get rid of the scans perhaps a defrag will help.

Christiaan Baes
Belgium

"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett