Alternative to replace the cursor
-
Hi,
I have written a function to retrieve a comma separated vaues of a column into my SSRS report.
I have used a cursor for this purpose.
Is there any alternate code to avoid cursor in my function?
CREATE FUNCTION [dbo].[fn_GetPrecautionaryStatementCodes]
(
@MaterialItemContainerCode nvarchar(1000)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
-- Declare variables
DECLARE @precautionaryStatementCursor CURSOR
DECLARE @precautionaryStatementCode NVARCHAR(50)
DECLARE @precautionaryStatementCodes NVARCHAR(MAX)
SET @precautionaryStatementCursor = CURSOR FOR
SELECT DISTINCT PS.precautionaryStatementCode FROM dbo.Local_PrecautionaryStatements PS
INNER JOIN dbo.Local_MaterialsItemsPrecautionaryStatements MIPS
ON PS.precautionaryStatementIncId = MIPS.precautionaryStatementIncId AND PS.precautionaryStatementSqlId = MIPS.precautionaryStatementSqlId
INNER JOIN Local_MaterialsItems MI
ON MI.materialItemIncId = MIPS.materialItemIncId AND MI.materialItemSqlId = MIPS.materialItemSqlId
INNER JOIN Local_MaterialsItemsContainers MIC
ON MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId
WHERE charindex(MIC.materialItemContainerCode,@MaterialItemContainerCode)<>0 AND MIC.isDeleted=0x0
SET @precautionaryStatementCodes = ''
-- Code
OPEN @precautionaryStatementCursor
FETCH NEXT FROM @precautionaryStatementCursor INTO @precautionaryStatementCode
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF(@precautionaryStatementCodes <> '')
SET @precautionaryStatementCodes = @precautionaryStatementCodes + ', '
SET @precautionaryStatementCodes = @precautionaryStatementCodes + @precautionaryStatementCode
FETCH NEXT FROM @precautionaryStatementCursor INTO @precautionaryStatementCode
END
CLOSE @precautionaryStatementCursor
RETURN @precautionaryStatementCodes
END
-
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
There are a number of ways to do this without a cursor. Here's a great article:
Concatenating Row Values in Transact-SQL[/url]
You could use the FOR XML PATH method and rewrite your Scalar Valued function like this:
CREATE FUNCTION [dbo].[fn_GetPrecautionaryStatementCodes](
@MaterialItemContainerCode nvarchar(1000)
)
RETURNS nvarchar(MAX) --CHANGE TO nvarchar(4000) if you can get away with it
AS
BEGIN
RETURN((
SELECT DISTINCT PS.precautionaryStatementCode
FROM dbo.Local_PrecautionaryStatements PS
INNER JOIN dbo.Local_MaterialsItemsPrecautionaryStatements MIPS
ON PS.precautionaryStatementIncId = MIPS.precautionaryStatementIncId AND PS.precautionaryStatementSqlId = MIPS.precautionaryStatementSqlId
INNER JOIN Local_MaterialsItems MI
ON MI.materialItemIncId = MIPS.materialItemIncId AND MI.materialItemSqlId = MIPS.materialItemSqlId
INNER JOIN Local_MaterialsItemsContainers MIC
ON MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId
WHERE charindex(MIC.materialItemContainerCode,@MaterialItemContainerCode)<>0 AND MIC.isDeleted=0x0
FOR XML PATH('')))
END
...or, you could make it a inline table valued function which will likely perform quicker like this.
CREATE FUNCTION [dbo].[fn_GetPrecautionaryStatementCodes](
@MaterialItemContainerCode nvarchar(max) --AGAIN >> CHANGE TO nvarchar(4000) if you can get away with it
)
RETURNS TABLE
AS
RETURN
(SELECT TOP 1
(SELECT DISTINCT PS.precautionaryStatementCode
FROM dbo.Local_PrecautionaryStatements PS
INNER JOIN dbo.Local_MaterialsItemsPrecautionaryStatements MIPS
ON PS.precautionaryStatementIncId = MIPS.precautionaryStatementIncId AND PS.precautionaryStatementSqlId = MIPS.precautionaryStatementSqlId
INNER JOIN Local_MaterialsItems MI
ON MI.materialItemIncId = MIPS.materialItemIncId AND MI.materialItemSqlId = MIPS.materialItemSqlId
INNER JOIN Local_MaterialsItemsContainers MIC
ON MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId
WHERE charindex(MIC.materialItemContainerCode,@MaterialItemContainerCode)<>0 AND MIC.isDeleted=0x0
FOR XML PATH('')
) AS precautionaryStatementCodes
)
GO
This is a table valued function but, depending on how you need to use this function, can work exactly like a Scalar valued function. The value would be retrieved from the scalar valued function the same way you use it now (SELECT [dbo].[fn_GetPrecautionaryStatementCodes]('<param value>') with the inline table valued version you would retrieve the value like this: [SELECT * FROM dbo.fn_GetPrecautionaryStatementCodes('<param value>')'.
Here's a good article about what I am doing here: How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]
Note, there will be a comma at the end of the returned value. This can be quickly fixed but I have run out of time ;)...
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply