Ignore Parameter 2

chrissie1 · Aug 30, 2007

I have an SP

That goes something like this

Code:

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

ca8msm · Aug 30, 2007

Try:

Code:

WHERE (@kittype Is NULL Or kittype = @kittype)

You'll also have to add "= Null" to the parameter declaration to give it a default.

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

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

Otto · Aug 30, 2007

Code:

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

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

You can omit the kittype parameter (or give null).

onpnt · Aug 30, 2007

Change the Count(*) to a specific field to (unless this was just for example purposes. It can slow things down if the table is wide (as my recent DB(horrid)2 database server experience has taught me.

_{____________ signature below ______________

I am Tedward Keyboardhands!!!

You are a amateur developer until you realize all your code sucks.

Jeff Atwood}

ca8msm · Aug 30, 2007

Change the Count(*) to a specific field

Unless of course that specific field may contains nulls and you actually want those nulls to be included in the count (not that I've come across a situation where I've needed to do that though, but you never know!).

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

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

AlexCuse · Aug 30, 2007

Chrissie1, you've been wandering into this forum a bunch lately ;-)

When/if you pick a specific column to count, make sure it does not contain nulls or you could get a different result than count(*) will give.

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

chrissie1 · Aug 30, 2007

Mark, That works thanks. didn't even have to set kittype to NULL.

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 · Aug 30, 2007

Nope the column doesn't contain any nulls. And since speed is very important in this case. The SP has about 20 of these little counts.

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 · Aug 30, 2007

Thanks guys.

How didn't I think of this myself?

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

ca8msm · Aug 30, 2007

Yeah, actually it makes sense that you don't have to set the default. I was just looking at an example of mine that did have a default.

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

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

chrissie1 · Aug 30, 2007

But I guess it's better practice so I did it anyway.

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

AlexCuse · Aug 30, 2007

>>>But I guess it's better practice so I did it anyway.

Brilliant!

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

onpnt · Aug 30, 2007

Sorry about not adding the null factor to my post

_{____________ signature below ______________

I am Tedward Keyboardhands!!!

You are a amateur developer until you realize all your code sucks.

Jeff Atwood}

vbkris · Aug 30, 2007

one more point chris.

this is not to do with the code but the performance angle.

When u find time have a look at this thread thread183-1402329

Known is handfull, Unknown is worldfull

gmmastros · Aug 30, 2007

The SP has about 20 of these little counts.

Can you show us the whole thing? There may be a better way to get your 20 counts (better than 20 sub-queries).

-George

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

chrissie1 · Aug 30, 2007

Ok then my little friends, eat your heart out.
It's only 13 the others are derived/calculated and will be calculated clientside.

Code:

ALTER PROCEDURE [dbo].[spo_KitsGeneralStatistic] 
(
	@KitType varchar(36) = NULL
)
AS

DECLARE @Stock varchar(20)
DECLARE @UsedLaboPJ varchar(20)
DECLARE @NotUsedLaboPJ varchar(20)
DECLARE @ObjectsSampled varchar(20)
DECLARE @FactType11 varchar(5)
DECLARE @FactType17 varchar(5)
DECLARE @FactType30 varchar(5)
SET @StockID = 'Stock'
SET @UsedLaboPJ = 'Used labo PJ'
SET @NotUsedLaboPJ = 'Not used labo PJ'
SET @ObjectsSampled = 'Car(s)'
SET @FactType11 = '11'
SET @FactType17 = '17'
SET @FactType30 = '30'

SELECT     	
(	SELECT	COUNT(kitnumber)
	FROM	tbl_kit
	WHERE   kitstatus = @UsedLaboPJ 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	UsedKits

,( 	SELECT	COUNT(kitnumber)
	FROM    tbl_kit
	WHERE	kitstatus = @NotUsedLaboPJ 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	KitsNotReturned

,(	SELECT 	count(kitnumber)
	FROM 	tbl_kit
	WHERE   kitstatus = @UsedLaboPj 
		AND labcontact IS NOT NULL 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	FormsReturned

,(	SELECT	COUNT(FactType)
	FROM 	[tbl_kit]
	WHERE 	FactType = @FactType11 
		AND (@kittype IS NULL Or kittype = @kittype)
)
	AS 	NumberOfCP11

,(	SELECT 	COUNT(FactType)
	FROM 	tbl_kit
	WHERE	FactType = @FactType17 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	NumberOfCP17

,(	SELECT 	COUNT(FactType)
        FROM 	tbl_kit
	WHERE 	FactType = @FactType30 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	NumberOfCP30

,(	SELECT	COUNT(FactType)
	FROM	tbl_kit
        WHERE   FactType != @FactType11 
		AND FactType != @FactType17 
		AND FactType != @FactType30
		AND FactType IS NOT NULL 
		AND (@kittype IS NULL Or kittype = @kittype) 
)
	AS 	NumberOfCPau

,(	SELECT	COUNT(FactType)
	FROM	tbl_kit
        WHERE   FactType IS NOT NULL
		AND (@kittype IS NULL Or kittype = @kittype)
)
	AS 	TotalCP

,(	SELECT 	AVG(Tapes)
	FROM 	tbl_kit
	WHERE 	Tapes > N'0' 
		AND (@kittype IS NULL Or kittype = @kittype)
)
	AS 	AverageTapes

,(	SELECT 	AVG([Enveloppes])
	FROM 	[tbl_kit]
	WHERE 	Enveloppes > N'0' 
		AND (@kittype IS NULL Or kittype = @kittype)
)
	AS 	AverageEnveloppes

,(	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

,(	SELECT 	SUM(Tapes)
	FROM	tbl_kit
	WHERE 	Tapes > N'0' 
		AND (@kittype IS NULL Or kittype = @kittype)
) 
	AS 	TotalTapes

,(	SELECT 	SUM(Enveloppes)
	FROM 	tbl_kit
	WHERE 	Enveloppes > N'0' 
		AND (@kittype IS NULL Or kittype = @kittype)
) 
	AS 	TotalEnveloppes

This now runs in less then a second even when I do this. The table has about 12000 records in it for the moment growing with a couple of thousand a year so nothing drastic.

I will try and make up some test data if you like?

Code:

exec spo_KitsGeneralStatistic null
Go

DECLARE @kittype varchar(36)
SET @kittype = (SELECT Id FROM tbl_Kittype WHERE Kittype = 'Normal')
EXEC spo_KitsGeneralStatistic @kittype
Go

DECLARE @kittype varchar(36)
SET @kittype = (SELECT Id FROM tbl_Kittype WHERE Kittype = '1:1')
EXEC spo_KitsGeneralStatistic @kittype
Go

DECLARE @kittype varchar(36)
SET @kittype = (SELECT Id FROM tbl_Kittype WHERE Kittype = 'Hair')
EXEC spo_KitsGeneralStatistic @kittype
Go

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 · Aug 30, 2007

Chrissie,

Try running this query...

Code:

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

I took the first 4 of your counts and reworked it a little. The 'trick' here is that a COUNT is the same as a sum when all the values for a sum are equal to 1.

I encourage you to change the remaining 'little counts' in to the format I show. Then compare the execution times. I suspect you will find that this method is considerably faster than the sub-query method while returning the same information.

Please give it a try. I think you'll be pleasantly surprised.

-George

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

chrissie1 · Aug 30, 2007

Thanks Geroge.

That is a lot faster. The execution plan is a lot simpler to.

Just one of them that didn't fit in there.

This one

Code:

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

because of the join.

Can I output this in the same SP? together with the other results?

Sorry but you guys made me a bit lazy.

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 · Aug 30, 2007

Sure. Just do that one the same way you always did. Like this...

Code:

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
[!],
(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

-George

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

chrissie1 · Aug 30, 2007

Thanks.

what took you so long ;-).

I actually figured it out myself.

It is fast just one more little tiny thing that bothers me. I get an index scan on kittype. Any idea why? Is it avoidable?

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Ignore Parameter 2

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor