title: Convert PL SQL function to T-SQL with cursor loop and fetch
body: <p>The code for the function is as follows:</p>
  <pre><code>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;
  </code></pre>
  <p>My attempt at translating it is below:</p>
  <pre><code>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
  </code></pre>
  <p>The problem is with, I think, how I'm trying to FETCH the value - even if I put a &quot;NEXT&quot; in there it doesn't work right. I've tried like 6 different ways to arrange the FETCH and none of them work.</p>