Convert PL SQL function to T-SQL with cursor loop and fetch

Question

The code for the function is as follows:

CREATE OR REPLACE FUNCTION FTTH_GETBUSZONECODEMULTI(
    p_house_nbr        IN VARCHAR2,
    p_as_of_date       IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2
AS
    CURSOR l_get_cur
    IS
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
           [not wasting your time with business logic]
    l_return VARCHAR2(32767);
BEGIN
    --
    FOR l_get_rec IN l_get_cur LOOP
       l_return := l_return || '|' || l_get_rec.BusinessZoneCode;
    END LOOP;
    --
    CASE
              WHEN l_return IS NULL THEN RETURN NULL;
              ELSE RETURN l_return || '|';
    END CASE;
    --
END FTTH_GETBUSZONECODEMULTI;

My attempt at translating it is below:

CREATE FUNCTION ftth_GETBUSZONECODEMULTI(
    @p_house_nbr        VARCHAR(4000),
    @p_as_of_date       DATETIME)
RETURNS VARCHAR(4000)
AS
BEGIN
   SET @p_as_of_date = GETDATE()
    DECLARE l_get_cur CURSOR LOCAL
    FOR
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
            [not wasting your time with business logic]
    DECLARE @l_return VARCHAR(MAX);
 
    --
    SET @l_return = isnull(@l_return, '') + '|' + ISNULL((FETCH BusinessZoneCode from l_get_cur), '');

    --
    if @l_return IS NULL begin RETURN NULL END
    if @l_return is not null BEGIN RETURN isnull(@l_return, '') + '|' END;
END

The problem is with, I think, how I'm trying to FETCH the value - even if I put a "NEXT" in there it doesn't work right. I've tried like 6 different ways to arrange the FETCH and none of them work.

Do you really want a CURSOR in a multi-line scalar function? That will perform awfully. You want an inline table-value function. — Thom A
– Thom A ♦, Commented Oct 11, 2020 at 18:27
Is this just simple string concatenation / aggregation? And always identify the version and edition of sql server you are using. TSQL has had a TRIM function for quite some time. — SMor
– SMor, Commented Oct 11, 2020 at 18:35
Beats me as to what it's for, I'm just a migration monkey doing one function after another ad nauseum, but I know I can't use a table-valued function because it gets called in a select statement down the line. It won't be performant at all, but the client set a super tight deadline and speed wasn't part of the deal lol — CapnShanty
– CapnShanty, Commented Oct 11, 2020 at 19:00
"Beats me as to what it's for," then find or work out what it does; if you're migrating it you need to understand it. — Thom A
– Thom A ♦, Commented Oct 11, 2020 at 19:17

eshirvana · Accepted Answer · 2020-10-11 18:39:19Z

1

here is some changes in cursor usage, I might be wrong where you cursor loop end , so you might need to adjust it:

CREATE FUNCTION ftth_GETBUSZONECODEMULTI(
    @p_house_nbr        VARCHAR(4000),
    @p_as_of_date       DATETIME)
RETURNS VARCHAR(4000)
AS
BEGIN
    declare @BusinessZoneCode varchar(500)
   SET @p_as_of_date = GETDATE()
    DECLARE l_get_cur CURSOR LOCAL
    FOR
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
            [not wasting your time with business logic]

    OPEN l_get_cur
    fetch next from l_get_cur
    into @BusinessZoneCode

    WHILE @@FETCH_STATUS = 0  
    begin 
        DECLARE @l_return VARCHAR(MAX);
 
        --
        SET @l_return = isnull(@l_return, '') + '|' + ISNULL((@BusinessZoneCode), '');

        --
        if @l_return IS NULL begin RETURN NULL END
        if @l_return is not null BEGIN RETURN isnull(@l_return, '') + '|' END

        fetch next from l_get_cur
        into @BusinessZoneCode
    end
    close l_get_cur
    deallocate l_get_cur
END

answered Oct 11, 2020 at 18:39

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CapnShanty Over a year ago

Ah I understand how to do it now! The fetch needs to go elsewhere and then just use a variable. Thanks!

Thom A Over a year ago

Though this probably answers the question, @CapnShanty, I strongly recommend that it is not the real solution. Using an inline table-value function is the real solution here,#

CapnShanty Over a year ago

Afaik, table valued functions can't get used as part of select statements, right? So I'm stuck with a scalar I think unfortunately

Dale K Over a year ago

@CapnShanty of course inline tabled valued functions can be used as part of a select. Thats why Larnu wanted to know what the purpose of the function was - so he/we could assist in creating one.

Collectives™ on Stack Overflow

Convert PL SQL function to T-SQL with cursor loop and fetch

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related