sloan
3/22/2007 8:39:00 PM
Avoid cursors ..........
There is 99.9% a way to do whatever you need without a cursor.
I've written 1 cursor in 7 years.
alternatives:
derivedTables
UserDefinedFunction
are alternatives.
Here is a derivedTable example:
Use Northwind
GO
-- select * from customers where Country='USA' order by City
set nocount on
--AllCount Number of Customers Per Country
--LondonOnlyCount -- Number of Customers In London specifically. ( which
means, only UK will have a value )
--StartsWithPContactNamesCount -- Number of Customers whose contact name
starts with P... Per Country
declare @returnValues table ( entryid int IDENTITY (1,1) , Country
varchar(24) , AllCount int , LondonOnlyCount int ,
StartsWithPContactNamesCount int )
--Give me something to work with, which is distinct county names
insert into @returnValues ( Country , AllCount , LondonOnlyCount ,
StartsWithPContactNamesCount )
Select distinct Country , 0 , 0 , 0 from dbo.customers
Update @returnValues
Set
AllCount = derivedTable1.PerCountryAllCount
From
@returnValues vart ,
( Select Country , count(*) as PerCountryAllCount from dbo.customers
innerC group by Country )
as derivedTable1
Where
vart.Country = derivedTable1.Country
Update @returnValues
Set
StartsWithPContactNamesCount = derivedTable2.PerCountryCount
From
@returnValues vart ,
( Select Country , count(*) as PerCountryCount from dbo.customers innerC
where innerC.ContactName like 'P%' group by Country )
as derivedTable2
Where
vart.Country = derivedTable2.Country
Update @returnValues
Set
LondonOnlyCount = derivedTable3.PerCountryLondonOnlyCount
From
@returnValues vart ,
( Select Country , count(*) as PerCountryLondonOnlyCount from
dbo.customers innerC where innerC.City like 'London' group by Country )
as derivedTable3
Where
vart.Country = derivedTable3.Country
-- OR all together
Update @returnValues
Set
StartsWithPContactNamesCount = derivedTable2.PerCountryCount
From
@returnValues vart
,
( Select Country , count(*) as PerCountryAllCount from dbo.customers
innerC group by Country )
as derivedTable1
,
( Select Country , count(*) as PerCountryCount from dbo.customers innerC
where innerC.ContactName like 'P%' group by Country )
as derivedTable2
,
( Select Country , count(*) as PerCountryLondonOnlyCount from
dbo.customers innerC where innerC.City like 'London' group by Country )
as derivedTable3
Where
vart.Country = derivedTable1.Country
and
vart.Country = derivedTable2.Country
and
vart.Country = derivedTable3.Country
select Country , AllCount , LondonOnlyCount ,
StartsWithPContactNamesCount from @returnValues
"FARRUKH" <farrscorpio77@hotmail.com> wrote in message
news:D8EEB0E4-DBCA-4E62-9D0D-44927DAE0434@microsoft.com...
> wht are the advantages of using cursor and disadvantages? is there any
> alternatives
>
> thanks