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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-19 : 17:51:58
|
Kajsa writes "Earlier in my they have been using temp tables where counting statistics:
CREATE PROCEDURE usp_sumABC
AS
Create Table #tblSubSet1 (A int, B char(50), C numeric)
Insert Into #tblSubSet1 (A, B, C)
(select X. ID, X.Name, sum(Y.sCol)
from dbo.TableY Y
join dbo.TableX X on X.ID = Y.ID
group by X.ID, X.Name)
-- if sum doesn´t retur any values replace with a 0
select A, B, C
from #tblSubSet1
union
select A = X.ID, B = X.Name, C = 0
from dbo.TableX X
where not exists(select * from #tblSubSet1 Sub where X.ID = Sub.ID)
order by 2
Drop table #tblSubset1
I started wondering if the following wouldn’t bee more effective:
CREATE PROCEDURE usp_sumABC
AS
select A = X. ID, B = X.Name, C = coalesce(sum(Y.sCol), 0)
from dbo.TableY Y
join dbo.TableX X on X.ID = Y.ID
group by X.ID, X.Name
order by X.Name
Is there a better way to write??? Is three a problem with coalesce besides the fact that it isn’t ANSI-SQL? I need to know before I start rewriting all 54 stored procedures.
/Kajsa" |
|
|
|
|
|
|
|
COALESCE and efficiency