asked on

Sql Server function with cursor

Hi ,
Can you please provide me with the equivalent Sql Server function for the Oracle function given below .
( Note : Thru the answer to another question in the link , I have an idea of how to write function . But All I am really looking for is how to use cursor
https://www.experts-exchange.com/questions/28486708/Get-equivalent-Sql-Server-Query-for-given-Oracle-Query.html
)

Please see the oracle query below

create or replace 
Function GetMultiObjs
(
  pParentID       IN  VARCHAR2,
  pID          IN  VARCHAR2,
  pDir       IN  VARCHAR2 DEFAULT '12'
)
  RETURN VARCHAR2 
IS 
  mRetVal    VARCHAR2(2000);

  CURSOR c1 IS
    SELECT  obj2.ObjName
    FROM    datarel rel, dataobj obj2
    WHERE Rel.uid1 = pParentID
            AND rel.uid2 = obj2.objuid
            AND rel.defuid = pID
          
  CURSOR c2 IS
    SELECT  obj2.ObjName
    FROM    datarel rel, dataobj obj2
    WHERE Rel.uid2 = pParentID
            AND rel.uid1 = obj2.objuid
            AND rel.defuid = pID
          
BEGIN
  mRetVal := '';
  BEGIN
    IF pDir = '12' THEN
      FOR rec IN c1 LOOP
          If mRetVal IS NULL Then
            mRetVal := rec.ObjName;
          ELSE
            mRetVal := mRetVal || '|' || rec.ObjName;
          END IF;
      END LOOP;
    ELSE
      FOR rec IN c2 LOOP
          If mRetVal IS NULL Then
            mRetVal := rec.ObjName;
          ELSE
            mRetVal := mRetVal || '|' || rec.ObjName;
          END IF;
      END LOOP;
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      mRetVal := '';
    WHEN OTHERS THEN
      mRetVal := '!!!ERR!!!';
  END;

  RETURN mRetVal;
END GetMultiObjs;

Open in new window

Guy Hengel [angelIII / a3]

> But All I am really looking for is how to use cursor
why would you use a cursor if that is the least efficient solution?
especially as here, it's to return a single value, which should be done with a simple select ...

Sam OZ

ASKER

Hi Thanks for the reply . can you please guide me with how to do it with cursor ? ( I don't need the whole Function - Just the Declaration and looping thru cursor

If you think , it can be done by a simple Query, Can you please provide that query ?

thanks

Sam

Guy Hengel [angelIII / a3]

please see this article which shows the difference between cursor processing (and the general non-cursor replacement):
https://www.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html
note: row by row processing is only needed for things where really, one row at a time needs to be "processed", and not the whole set

in your case, you don't have multiple rows to be processed, just 1 single values to be returned, hence cursor is not to be used. even in Oracle you don't need cursors for this kind of functions, just a big waste of resources.

in regards to the "simple query", I see that in the linked question you got already the answer there

ste5an

Afaik, even in PL/SQL you can write that in a single, recursive statement (CTE).

Sam OZ

ASKER

Hi ,
Can you please provide me the query ? It is different from the previous one in just one aspect
( As I told before , I need only the cursor part of the query - If simple PL/SQL can do it , Please provide me that )

The core logic in this is

Loop All Rows
If mRetVal IS NULL Then --- First Row
mRetVal := rec.ObjName;
ELSE --- Keep appending to RetVal with a Delim + ObjName
mRetVal := mRetVal || '|' || rec.ObjName;
END IF;

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sam OZ

ASKER

Hi Thanks for all valuable comments . Can someone help me with the For XML query ( I am on a tight timeline and really don't have time for trial and error)

Can someone please give me the For XML equivalent of just one loop

FOR rec IN c1 LOOP
If mRetVal IS NULL Then
mRetVal := rec.ObjName;
ELSE
mRetVal := mRetVal || '|' || rec.ObjName;
END IF;
END LOOP;

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

oops I got this wrong way around, oh well. Sorry.

But: you do not need a cursor in Oracle, and you don't need one in SQL Server either.

i.e. You can replace that Oracle cursor code with a query using listagg()

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial