Solved

Sql Server function with cursor

Posted on 2014-07-30
11
442 Views
Last Modified: 2014-08-03
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
                   http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28486708.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

0
Comment
Question by:Sam OZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231124
> 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 ...
0
 

Author Comment

by:Sam OZ
ID: 40231135
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231153
please see this article which shows the difference between cursor processing (and the general non-cursor replacement):
http://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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Expert Comment

by:ste5an
ID: 40231160
Afaik, even in PL/SQL you can write that in a single, recursive statement (CTE).
0
 

Author Comment

by:Sam OZ
ID: 40231190
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;
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 40231200
in oracle, you use listagg function:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm

in sql server, you can do this using the FOR XML subquery, see here for example
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_28272543.html
0
 

Author Comment

by:Sam OZ
ID: 40231514
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;
0
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 100 total points
ID: 40231572
E.g.
DECLARE @ID INT;
DECLARE @ParentID INT;

SELECT  STUFF(( SELECT  '\' + obj2.ObjName
                FROM    datarel rel
                        INNER JOIN dataobj obj2 ON rel.uid1 = obj2.objuid
                WHERE   Rel.uid2 = @ParentID
                        AND rel.defuid = @ID
              FOR
                XML PATH('')
              ), 1, 1, '');

Open in new window


Caveat: Your Oracle solution and the above T-SQL solution guarentee no order!
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 40231589
LISTAGG()

is the "equivalent"

of "FOR XML PATH" in sql server

Plus: you don't need it "for just one loop" because LISTAGG() will handle ALL the loops*

*actually it won't be loops that you use, just a simple query

    CREATE TABLE DATAOBJ
    	("OBJNAME" varchar2(8))
    ;
    
    INSERT ALL 
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('askjakja')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('aasdasd')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('asdfsfds')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('dfdfgdf')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('dfgdfg')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('dfgdfg')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('dfgdfg')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('rehtrt')
    	INTO DATAOBJ ("OBJNAME")
    		 VALUES ('dfdthrr')
    SELECT * FROM dual
    ;

Open in new window

**Query 1**:

    SELECT
           LISTAGG(OBJNAME, ' | ')
             WITHIN GROUP (ORDER BY OBJNAME) AS "Objname_list"
    FROM DATAOBJ
    

**[Results][2]**:
    
    |                                                                          OBJNAME_LIST |
    |---------------------------------------------------------------------------------------|
    | aasdasd | asdfsfds | askjakja | dfdfgdf | dfdthrr | dfgdfg | dfgdfg | dfgdfg | rehtrt |



  [1]: http://sqlfiddle.com/#!4/dd1e9/1

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40231613
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()
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 150 total points
ID: 40231753
CREATE FUNCTION GetMultiObjs
--ALTER FUNCTION GetMultiObjs
(
  @pParentID  nvarchar(50),
  @pID  nvarchar(50),
  @pDir  nvarchar(50) = '12'
)
RETURNS nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @return_value nvarchar(max)
SET @return_value = ''

SELECT @return_value = STUFF((
    SELECT '|' + obj2.ObjName
    FROM datarel rel
    INNER JOIN dataobj obj2 ON
        Rel.uid1 = @pParentID AND
        rel.uid2 = obj2.objuid AND
        rel.defuid = @pID
    ORDER BY obj2.ObjName
    FOR XML PATH('')
    ), 1, 1, '')

SELECT @return_value = @return_value + STUFF((
    SELECT '|' + obj2.ObjName
    FROM datarel rel
    INNER JOIN dataobj obj2 ON
        Rel.uid2 = @pParentID AND
        rel.uid1 = obj2.objuid AND
        rel.defuid = @pID
    ORDER BY obj2.ObjName
    FOR XML PATH('')
    ), 1, 1, '')
   
RETURN @return_value
END --FUNCTION
GO
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question