with out the cursors - 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

SQL Server 2005 Forums

Transact-SQL (2005)

with out the cursors

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-06-01 : 09:53:36

Hello,
I have one UDF(user defined function) written in SQL Server which is using the Cursors. it is very slow in returning the results.
can any one help in the converting this by using some other concept like Temporary tables etc.

-SELECT distinct(territory_id),rpt_lvl1_terr,Level FROM dbo.GetTERRITORRYHierarchy(1,NULL, 'MMRM',0) where level <>0 order by territory_id--where level =2
create FUNCTION [dbo].[GetTERRITORRYHierarchy](@IncludeParent bit,@territory_id int, @role_cd nvarchar(100),@Level int)
RETURNS @retFindReports TABLE (territory_id int, rpt_lvl1_terr int, Level int)
AS
BEGIN
IF (@IncludeParent=1 AND @role_cd IS NOT NULL)
BEGIN
INSERT INTO @retFindReports
--SELECT territory_id ,rpt_lvl1_terr FROM da_ff_territory WHERE role_cd='MMRM'
SELECT territory_id ,rpt_lvl1_terr,@Level FROM da_ff_territory WHERE role_cd=@role_cd
END

SET @Level = @Level + 1

DECLARE @Report_territory_id int, @Report_rpt_lvl1_terr int, @Report_Level int

IF @role_cd IS NOT NULL
BEGIN
INSERT INTO @retFindReports
SELECT territory_id ,rpt_lvl1_terr,@Level FROM da_ff_territory WHERE role_cd=@role_cd

DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT territory_id ,rpt_lvl1_terr,@Level FROM da_ff_territory WHERE role_cd=@role_cd
END
ELSE
BEGIN
INSERT INTO @retFindReports
SELECT territory_id ,rpt_lvl1_terr,@Level FROM da_ff_territory WHERE rpt_lvl1_terr=@territory_id

DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT territory_id ,rpt_lvl1_terr,@Level FROM da_ff_territory WHERE rpt_lvl1_terr=@territory_id
END

OPEN RetrieveReports

FETCH NEXT FROM RetrieveReports
INTO @Report_territory_id, @Report_rpt_lvl1_terr, @Report_Level

WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.GetTERRITORRYHierarchy(0,@Report_territory_id,NULL,@Level)

FETCH NEXT FROM RetrieveReports
INTO @Report_territory_id, @Report_rpt_lvl1_terr, @Report_Level
END

CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards and Thanks,
Krishna.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 10:52:04

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources