function with cursor in sql server

Question

I have written a SQL scalar function with a cursor to retrieve item names with comma separated values. This is my function but it gives me an error.

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO

ALTER FUNCTION [dbo].[GET_ITEM_NAME]
    (
      @PRINT_RECEIPT_MST_ID INT
    )
RETURNS VARCHAR(1000)
AS
    BEGIN
        DECLARE @ITEM_TITLE VARCHAR(1000)
        DECLARE @ITEM VARCHAR(100)
        SET @ITEM_TITLE = '';
        DECLARE ItemTitleCursor CURSOR FAST_FORWARD
        FOR
            SELECT ISNULL(IMT.ITEM_TITLE,'')
            FROM PRINT_RECEIPT_DET_T PDT 
            LEFT OUTER JOIN ITEM_MST_T IMT ON IMT.ITEM_ID=PDT.ITEM_ID
            WHERE PDT.PRINT_RECEIPT_MST_ID = @PRINT_RECEIPT_MST_ID

        OPEN ItemTitleCursor

        FETCH NEXT FROM ItemTitleCursor INTO @ITEM

        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @ITEM_TITLE = '' )
                    BEGIN
                        SET @ITEM_TITLE = CAST(@ITEM AS VARCHAR(100));
                    END
                ELSE
                    BEGIN
                        SET @ITEM_TITLE = CAST(@ITEM_TITLE AS VARCHAR(1000))
                            + ' , ' + CAST(@ITEM AS VARCHAR(100));
                    END
                FETCH NEXT FROM ItemTitleCursor INTO @ITEM
            END


        CLOSE ItemTitleCursor
        DEALLOCATE ItemTitleCursor 

        RETURN @ITEM_TITLE

    END

_but it gives me error_ Maybe you could tell us what error you're getting? And please include the full code, with the `create function ...`part — jpw, Jan 30 '15 at 16:07
First, Cursors are rarely needed in SQL - as in only once in a decade, especially for simple queries. Second, the real question is how to aggregate strings - a duplicate of other similar questions. — Panagiotis Kanavos, Jan 30 '15 at 16:18
possible duplicate of [Optimal way to concatenate/aggregate strings](http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — Panagiotis Kanavos, Jan 30 '15 at 16:21

Pரதீப் · Answer 1 · 2015-01-30T16:17:04.463

If am i am not wrong this should replace your cursor

DECLARE @ITEM_TITLE           VARCHAR(1000)='',
        @PRINT_RECEIPT_MST_ID INT

SELECT @ITEM_TITLE = + Isnull(IMT.ITEM_TITLE, '') + ','
FROM   PRINT_RECEIPT_DET_T PDT
       LEFT OUTER JOIN ITEM_MST_T IMT
                    ON IMT.ITEM_ID = PDT.ITEM_ID
WHERE  PDT.PRINT_RECEIPT_MST_ID = @PRINT_RECEIPT_MST_ID

SELECT @Left(@ITEM_TITLE, Len(@ITEM_TITLE) - 1)

or Use For xml path to convert all the rows to csv

DECLARE @ITEM_TITLE           VARCHAR(1000)='',
        @PRINT_RECEIPT_MST_ID INT

SET @ITEM_TITLE=(SELECT Isnull(IMT.ITEM_TITLE, '') + ','
                 FROM   PRINT_RECEIPT_DET_T PDT
                        LEFT OUTER JOIN ITEM_MST_T IMT
                                     ON IMT.ITEM_ID = PDT.ITEM_ID
                 WHERE  PDT.PRINT_RECEIPT_MST_ID = @PRINT_RECEIPT_MST_ID
                 FOR XML PATH(''))

SELECT Left(@ITEM_TITLE, Len(@ITEM_TITLE) - 1)

This method to aggregate strings used undefined behavior – Panagiotis Kanavos Jan 30 '15 at 16:16 — Panagiotis Kanavos, Jan 30 '15 at 16:16

score -1 · Answer 2 · answered Jan 30 '15 at 16:18

-1

Thank you all for your quick responses! i have solved it.my cursor returns length more than i had mentioned.it should have been

RETURNS VARCHAR(4000)

answered Jan 30 '15 at 16:18

user632299

299
3
9
22

1

You solved the wrong problem. The real question is how to aggregate strings - the answer isn't a cursor. The fastest way is the XML PATH technique – Panagiotis Kanavos Jan 30 '15 at 16:20
may you please explain it well or may you please give me example of it.actually I am using Stored Procedure to bind jquery table.and I can bind only datatable to jquery table,so can't write extra property in partial class. that's why I decided to write Function with cursor. – user632299 Jan 30 '15 at 16:26

function with cursor in sql server

2 Answers2