Help with Proc - 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

Help with Proc

Author

Topic

TechNess
Starting Member

11 Posts

Posted - 2011-01-31 : 23:26:55

Hi All,

I am having a problem with procedure.
What could be the correction?
Here is my table:
ID Product Type
1 A 1
2 A 1
3 A 2
4 B 1
5 B 2
6 B 2
7 C 1

I wrote a stored procedure so that I my output will be like this:

Product Total Type1 Type2
A 3 2 1
B 3 1 2
C 1 1 0

Here is my procedure:
CREATE PROC myProcedure
@product varchar(10),
@type varchar(10)
AS
BEGIN
if (@product = '' or @product = '0') set @product = null
if (@type = '' or @type = '0') set @type = null

SELECT Product, COUNT(*) AS Total,
ISNULL((select COUNT(*) from Product where Product in (ISNULL(@product,Product)) and Type=1),0) AS Type1,
ISNULL((select COUNT(*) from Product where Product in (ISNULL(@product,Product)) and Type=2),0) AS Type2
From Product
Where
Product = isnull(@product,Product) AND
Type = isnull(@type, Type)
Group by Product
END

Here is the Output which is wrong: exec myProcedure '',''
Product Total Type1 Type2
A 3 4 3
B 3 4 3
C 1 4 3

when I execute as exec myProcedure 'A', '' it works fine
Product Total Type1 Type2
A 3 2 1

Thanks in Advance..

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 00:30:04

replace your select statement with this one

SELECT
product
,COUNT(Id) total
,sum(case when type=1 then 1 else 0 end) as Type1
,sum(case when type=2 then 1 else 0 end) as Type2
From @casetable
Where
Product = isnull(@product,Product) AND
Type = isnull(@type, Type)
Group by Product