Cursor in SQL 2008 - 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 2008 Forums

Transact-SQL (2008)

Cursor in SQL 2008

Author

Topic

del
Starting Member

1 Post

Posted - 2012-03-15 : 09:28:19

Hi, I have a report using the following function, the processing has slowed due to use of cursor, please let me know what I can do to increase the performance either by continuing using the cursor or another solution,
We are using a view containing sales details of different products so each time the report is run the function reads the product table containing thousands of records,

CREATE function [dbo].[fnSingleLineSalePCMSCodeCommission]
( --passes in these parameters
@SaleLineId int,
@PayableToUserId int,
@ComType Varchar(17),
@ActingRoleId int

)

returns varchar(max)
as

begin
declare @ref varchar(50)
declare @Amount decimal(10,2)
declare @str varchar(max)
declare @Product varchar(100)
Declare cRefs CURSOR
FOR
select
SaleLineId,reference, Amount,PayableToUserID,ComType,ActingRoleId,Product
from [TSA_TeleSales_Sales].[Domain].[vwPayEntitlementHistory]
where ComType <> 'Cancellations' and salelineid = @SaleLineId and PayableToUserId = @PayableToUserID and ComType = @ComType and ActingRoleId = @ActingRoleID
group by SaleLineId,reference, Amount,PayableToUserID,Comtype,ActingRoleId,Product
OPEN cRefs
set @str = ''

FETCH NEXT from cRefs INTO @SaleLineId, @Ref, @Amount,@PayableToUserID,@ComType,@ActingRoleId,@Product

declare @strLen int

WHILE @@FETCH_STATUS <> -1

Begin

if (@Ref is not null) set @str = @str + @Product + '(' + cast(@Amount as varchar(15)) + '), '

FETCH NEXT from cRefs INTO @SalelineId, @Ref, @Amount,@PayableToUserID,@ComType,@ActingRoleId,@Product

End
CLOSE cRefs
DEALLOCATE cRefs

set @strLen = len(@str)
if (@strLen > 1) set @str = substring(@str, 0, @strLen)

return @str
--exec sp_executesql N'select Domain.vwPayEntitlements.*,Dbo.PayEntitlementsSaleRefs.* from Domain.vwPayEntitlements left join Dbo.PayEntitlementsSaleRefs on Domain.vwPayEntitlements.SaleId = Dbo.PayEntitlementsSaleRefs.Saleid'
End

Await your reply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-15 : 09:50:57

[code]CREATE FUNCTION dbo.fnSingleLineSalePCMSCodeCommission
(
@SaleLineID INT,
@PayableToUserID INT,
@ComType VARCHAR(17),
@ActingRoleID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT STUFF(d.Data, 1, 2, '')
FROM (
SELECT ', ' + Product + ' (' + CAST(Amount AS VARCHAR(15)) + ')'
FROM TSA_TeleSales_Sales.Domain.vwPayEntitlementHistory
WHERE SaleLineID = @SaleLineID
AND PayableToUserID = @PayableToUserID
AND ActingRoleID = @ActingRoleID
AND ComType = @ComType
AND ComType <> 'Cancellations'
GROUP BY SaleLineID,
Reference,
Amount,
PayableToUserID,
ComType,
ActingRoleID,
Product
FOR XML PATH('')
) AS d(Data)
)
END[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources