meme
Ahem
---
I'm currently trying to replace a scalar UDF with either an inline table valued function, or a multi-statement table valued function, to reduce some performance issues with a large outer query.
The reason for the loop and cursor inside the scalar udf is that there are some calculations that are performed where the current state has to be stored and then used in future iterations of the loop.
The Repro
--
For maximum realism, this is how the variable number of rows are generated.
```
CREATE OR ALTER FUNCTION dbo.m(@num_rows int)
RETURNS @t table(id int)
AS
BEGIN
INSERT
@t (id)
SELECT TOP (@num_rows)
ROW_NUMBER() OVER
(
ORDER BY
1/0
) AS n
FROM sys.messages AS m;
RETURN;
END;
GO
```
Testing, testing.
```
SELECT
m.id
FROM dbo.m(10) AS m;
GO
```
And now the UDF!
```
CREATE OR ALTER FUNCTION dbo.s(@i int)
RETURNS bigint
AS
BEGIN
/*table*/
DECLARE @t table(id int);
/*nonsense*/
DECLARE @r int = 0;
DECLARE @c int = 0;
DECLARE @p int = 0;
DECLARE @pp int = 0;
/*cursor*/
DECLARE @cid int = 0;
DECLARE @id int = 0;
/*Rows and rows*/
INSERT
@t (id)
SELECT
m.id
FROM dbo.m(@i) AS m;
/*Outer loop*/
WHILE @c <= 1
BEGIN
/*Cursor nonsense*/
DECLARE c CURSOR FOR
SELECT t.id FROM @t AS t;
OPEN c;
FETCH c INTO @cid;
WHILE @@FETCH_STATUS = 0
BEGIN
/*Some maths*/
SET @p = @cid + @r - (@pp + @id);
SET @pp += @p;
SET @id += @cid;
FETCH c INTO @cid;
END;
DEALLOCATE c;
/*Some final maths*/
SET @r += @cid;
SET @c += 1;
END;
RETURN @r;
END;
GO
```
Some test runs, too.
```
SELECT
dbo.s(5) AS s;
SELECT
dbo.s(10) AS s;
SELECT
dbo.s(15) AS s;
SELECT
dbo.s(20) AS s;
```