CODEWITHSUNDEEP
sqlsql-serverfunctioncursorscalar
Marina Gratiela Stan
Marina Gratiela Stan

Reputation: 59

SqlServer 2019 using table and scalar function into cursor

I occurred a strange error:

Msg 596, Level 21, State 1, Line 0

Cannot continue the execution because the session is in the kill state.

It seems that error is generated when I use a cursor like :

DECLARE dbCursor CURSOR
            FOR 
                SELECT (SELECT TOP 1 VAL FROM dbo.fn_Table('n:',dbo.[fn_Scalar](var)) ORDER BY VAL) 
                from table

If I taken the select to run it, it is ok.

If in cursor I execute only fn_Table OR fn_Scalar, it is ok.

If in cursor I execute BOTH functions, I have the above error.

In older versions of SQL server (than 2019) the cursor is running properly.

Could you, please, give me an idea?

Thank you.

L.E. The functions are: ALTER function [dbo].[UrlDecode](@url nvarchar(max)) returns nvarchar(max) as begin declare @foo datetime=getdate();

DECLARE @Position INT, @Base CHAR(16), @High TINYINT, @Low TINYINT, @Pattern CHAR(21)

SELECT  @Base = '0123456789abcdef',
    @Pattern = '%[%][0-9a-f][0-9a-f]%',
    @URL = REPLACE(@URL, '+', ' '),
    @Position = PATINDEX(@Pattern, @URL)

WHILE @Position > 0
    SELECT  @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base COLLATE Latin1_General_CI_AS),
        @Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base COLLATE Latin1_General_CI_AS),
        @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
        @Position = PATINDEX(@Pattern, @URL)

RETURN  @URL

end

ALTER FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE ( val VARCHAR(MAX) )

AS BEGIN

--declare @foo datetime=getdate();
--Remove separator if the case
IF RIGHT(LTRIM(RTRIM(@s)),1) = @sep 
    SET @s = LEFT(LTRIM(RTRIM(@s)), LEN(LTRIM(RTRIM(@s))) - 1);

DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

INSERT INTO @t(val)
SELECT r.value('.','VARCHAR(250)') as Item
FROM @xml.nodes('//root/r') AS RECORDS(r)

RETURN

END

And the cursor is: DECLARE cursor_product CURSOR FOR select (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode) ORDER BY VAL) from ABSENTEE

OPEN cursor_product;

FETCH NEXT FROM cursor_product WHILE @@FETCH_STATUS = 0 BEGIN

    FETCH NEXT FROM cursor_product
END;

CLOSE cursor_product; DEALLOCATE cursor_product;

Please, ignore how is defined the cursor, the error occours, anyway. Thank you.

Upvotes: 0

Views: 610

Answers (2)

lptr
lptr

Reputation: 6788

As it stands, you'll have to change the cursor's select statement (scalar udf-inlining was just a blind guess [function+sql2019 not working, but works pre-sql2019] , the problem is the cursor's execution) :

Option 1: 

The answer of Marc Guillot (populate a table and iterate over that)

Option 2: APPLY( function calls)

DECLARE cursor_product CURSOR FOR 
select f.VAL
from ABSENTEE as a
cross /*outer ?*/ apply
(
    SELECT TOP 1 VAL 
    FROM dbo.split('n:',dbo.UrlDecode(a.columnXYZ)) 
    ORDER BY VAL
) as f;

Option 3: aggregation on ABSENTEE table

DECLARE cursor_product CURSOR FOR 
select (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode(columnXYZ)) ORDER BY VAL) 
from ABSENTEE
group by ABSENTEE.columnXYZ, ABSENTEE.othercolumn(s)

Option 4: windowed aggregation on the function's result

DECLARE cursor_product CURSOR FOR 
select 
    MIN(
          (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode(columnXYZ)) ORDER BY VAL) 
       ) over(partition by columnXYZ)
from ABSENTEE

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6455

If the select alone runs well, I propose you to retrieve your data first into a table variable, so you can iterate it later, once the calls to your function are finished, avoiding any kind of weird locking between those calls.

DECLARE @MyData table (VAL int)

INSERT INTO @MyData (VAL)
       SELECT (SELECT TOP 1 VAL FROM dbo.fn_Table('n:',dbo.[fn_Scalar](var)) ORDER BY VAL) 
       FROM table

DECLARE dbCursor CURSOR FOR SELECT VAL FROM @MyData

Upvotes: 0

Related Questions