MS SQL S.P. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MS SQL S.P.

Author

Topic

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-09-28 : 05:34:58

Hi,

is there any other way to build the following condition on S.P.
if @catcode <> ''
begin
select * from item where catcode = @catcode
end

if @catcode <> '' and @subcode <> ''
begin
select * from item where catcode = @catcode and subcode = @subcode
end

if @catcode = '' and @subcode <> ''
begin
select * from item where subcode = @subcode
end

best Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 05:37:36

try this:
select * from item where (catcode = @catcode or @catcode = '') and (subcode = @subcode or @subcode = '')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 05:37:43

[code]select * from item where (@catcode = '' or catcode = @catcode) and
(@subcode = '' or subcode = @subcode)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-09-28 : 05:42:41

MS SQL have any case statment to do this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 05:46:22

quote:
Originally posted by issammansour

MS SQL have any case statment to do this.

Whats wrong with the suggested methods?

Madhivanan

Failing to plan is Planning to fail

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-09-28 : 05:53:27

Sorry I don't mean anything but the question for my information, any way thank you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 05:53:59

yes you also have a case when ... else ... end construct

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 06:08:33

[code]SELECT *
FROM Item
WHERE CatCode = COALESCE(NULLIF(@CatCode, ''), CatCode)
AND SubCode = COALESCE(NULLIF(@SubCode, ''), SubCode)[/code]

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 06:09:59

Peter,

Won't that cause Table Scan due to function over column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 06:11:56

I don't think so.
The function is over varible, not column.

Bu I may be wrong. The only thing to know for sure, is that OP posts proper and accurate sample data and expected output based on a set of valid parameters.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 06:23:24

"The function is over varible, not column"

Yes that's true about NULLIF, but what about COALESCE()?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 06:34:30

You are not COALESCING for two different columns, you are coalescing same colunm.
I can see if I get some time over to create some sample data.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:03:46

Testing time!

CREATE TABLE	#Item
		(
			CatCode INT,
			SubCode INT
		)

INSERT		#Item
		(
			CatCode,
			SubCode
		)
SELECT		TOP 100000
		1 + ABS(CHECKSUM(NEWID())) % 100,
		1 + ABS(CHECKSUM(NEWID())) % 100
FROM		master..syscolumns AS c1
CROSS JOIN	master..syscolumns AS c2

CREATE NONCLUSTERED INDEX IX_Item ON #Item (CatCode, SubCode)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:04:56

[code]DECLARE @CatCode INT,
@SubCode INT

SELECT @CatCode = 5,
@SubCode = 2

-- Spirit & Harsh
SELECT CatCode,
SubCode
FROM #Item
WHERE (CatCode = @CatCode OR @CatCode = 0)
AND (SubCode = @SubCode OR @SubCode = 0)

|--Table Scan(OBJECT:([tempdb].[dbo].[#Item]), WHERE:(([tempdb].[dbo].[#Item].[CatCode]=[@CatCode] OR [@CatCode]=(0)) AND ([tempdb].[dbo].[#Item].[SubCode]=[@SubCode] OR [@SubCode]=(0))))[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:05:45

[code]DECLARE @CatCode INT,
@SubCode INT

SELECT @CatCode = 5,
@SubCode = 2

-- Peso 1
SELECT CatCode,
SubCode
FROM #Item
WHERE CatCode = COALESCE(NULLIF(@CatCode, 0), CatCode)
AND SubCode = COALESCE(NULLIF(@SubCode, 0), SubCode)

|--Table Scan(OBJECT:([tempdb].[dbo].[#Item]), WHERE:([tempdb].[dbo].[#Item].[CatCode]=CASE WHEN CASE WHEN [@CatCode]=(0) THEN NULL ELSE [@CatCode] END IS NOT NULL THEN CASE WHEN [@CatCode]=(0) THEN NULL ELSE [@CatCode] END ELSE [tempdb].[dbo].[#Item].[CatCode] END AND [tempdb].[dbo].[#Item].[SubCode]=CASE WHEN CASE WHEN [@SubCode]=(0) THEN NULL ELSE [@SubCode] END IS NOT NULL THEN CASE WHEN [@SubCode]=(0) THEN NULL ELSE [@SubCode] END ELSE [tempdb].[dbo].[#Item].[SubCode] END))[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:06:38

[code]DECLARE @CatCode INT,
@SubCode INT

SELECT @CatCode = 5,
@SubCode = 2

-- Peso 2
SELECT CatCode,
SubCode
FROM #Item
WHERE CatCode = @CatCode
AND SubCode = @SubCode

UNION ALL

SELECT CatCode,
SubCode
FROM #Item
WHERE CatCode = @CatCode
AND @SubCode = 0

UNION ALL

SELECT CatCode,
SubCode
FROM #Item
WHERE @CatCode = 0
AND SubCode = @SubCode

|--Concatenation
|--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[CatCode]=[@CatCode] AND [tempdb].[dbo].[#Item].[SubCode]=[@SubCode]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@SubCode]=(0))))
| |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[CatCode]=[@CatCode]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@CatCode]=(0))))
|--Table Scan(OBJECT:([tempdb].[dbo].[#Item]), WHERE:([tempdb].[dbo].[#Item].[SubCode]=[@SubCode]))[/code]

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 07:11:49

Interesting to see SQL Server unwind NULLIF and COALESCE into series of CASE statements. Both the plans are more or less same though.

Thanks Peter.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:17:16

This is the fastest way to do it with sample data above. Create two nonclusted indexes.
If the records in #Item is in reality some hefty 15-or-more JOIN, results can differ in terms of READS.

CREATE TABLE	#Item
		(
			CatCode INT,
			SubCode INT
		)

INSERT		#Item
		(
			CatCode,
			SubCode
		)
SELECT		TOP 100000
		1 + ABS(CHECKSUM(NEWID())) % 100,
		1 + ABS(CHECKSUM(NEWID())) % 100
FROM		master..syscolumns AS c1
CROSS JOIN	master..syscolumns AS c2

CREATE NONCLUSTERED INDEX IX_Item1 ON #Item (CatCode, SubCode)
CREATE NONCLUSTERED INDEX IX_Item2 ON #Item (SubCode, CatCode)

DECLARE	@CatCode INT,
	@SubCode INT

SELECT	@CatCode = 0,
	@SubCode = 2

-- Spirit & Harsh
SELECT	CatCode,
	SubCode
FROM	#Item
WHERE	(CatCode = @CatCode OR @CatCode = 0)
	AND (SubCode = @SubCode OR @SubCode = 0)
	
-- Peso 1
SELECT	CatCode,
	SubCode
FROM	#Item
WHERE	CatCode = COALESCE(NULLIF(@CatCode, 0), CatCode)
	AND SubCode = COALESCE(NULLIF(@SubCode, 0), SubCode)


-- Peso 2
SELECT	CatCode,
	SubCode
FROM	#Item
WHERE	CatCode = @CatCode
	AND SubCode = @SubCode

UNION ALL

SELECT	CatCode,
	SubCode
FROM	#Item
WHERE	CatCode = @CatCode
	AND @SubCode = 0

UNION ALL

SELECT	CatCode,
	SubCode
FROM	#Item
WHERE	@CatCode = 0
	AND SubCode = @SubCode

DROP TABLE #Item


-- Peso 2
  |--Concatenation
       |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[SubCode]=[@SubCode] AND [tempdb].[dbo].[#Item].[CatCode]=[@CatCode]) ORDERED FORWARD)
       |--Filter(WHERE:(STARTUP EXPR([@SubCode]=(0))))
       |    |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[CatCode]=[@CatCode]) ORDERED FORWARD)
       |--Filter(WHERE:(STARTUP EXPR([@CatCode]=(0))))
            |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[SubCode]=[@SubCode]) ORDERED FORWARD)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:26:49

Also Peso 2 is the only one working when sending @CatCode = 0 and @SubCode = 0.
According to OP, nothing should be returned.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:39:06

Fastest yet!
And returns empty resultset when sending both parameters as 0.

-- Peso 3
SELECT	CatCode,
	SubCode
FROM	#Item WITH (INDEX (IX_Item1))
WHERE	@CatCode = CatCode
	AND (@SubCode = SubCode OR @SubCode = 0)
UNION ALL
SELECT	CatCode,
	SubCode
FROM	#Item WITH (INDEX (IX_Item2))
WHERE	(@CatCode = CatCode OR @CatCode = 0)
	AND @SubCode = SubCode

  |--Concatenation
       |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[CatCode]=[@CatCode]),  WHERE:([@SubCode]=[tempdb].[dbo].[#Item].[SubCode] OR [@SubCode]=(0)) ORDERED FORWARD)
       |--Index Seek(OBJECT:([tempdb].[dbo].[#Item]), SEEK:([tempdb].[dbo].[#Item].[SubCode]=[@SubCode]),  WHERE:([@CatCode]=[tempdb].[dbo].[#Item].[CatCode] OR [@CatCode]=(0)) ORDERED FORWARD)

CREATE TABLE	#Item
		(
			CatCode INT,
			SubCode INT
		)

INSERT		#Item
		(
			CatCode,
			SubCode
		)
SELECT		TOP 100000
		1 + ABS(CHECKSUM(NEWID())) % 100,
		1 + ABS(CHECKSUM(NEWID())) % 100
FROM		master..syscolumns AS c1
CROSS JOIN	master..syscolumns AS c2

CREATE NONCLUSTERED INDEX IX_Item1 ON #Item (CatCode, SubCode)
CREATE NONCLUSTERED INDEX IX_Item2 ON #Item (SubCode, CatCode)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:42:07

[code] CPU DURATION READS
--- -------- -----
S & H 47 45 215
Peso 3 0 1 9[/code]

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources