Solved

Sql Server Function with  temporary variables inside

Posted on 2014-07-30
6
366 Views
Last Modified: 2014-07-30
Hi Experts ,

    How can I get  the temporary variables in a function in Sql Server
    For example , My Oracle function is as follows    In fact what I was really looking is how  I use the variables like mRetVal  and mID in the equivalent Sql Server function
( I would be thankful if  you can provide me the Sql Server equivalent ? - eventhough not really essential )
Function GetNewObj
(
   ParentUID       IN  VARCHAR2,
   pDirection      IN  VARCHAR2,
   psFlow       IN  VARCHAR2
   )
   RETURN VARCHAR2
IS
  mRetVal   VARCHAR2(200);
  mID     VARCHAR2(200);
  mTemp     VARCHAR2(200);
  mTemp2    VARCHAR2(200);
BEGIN
  mRetVal := '';

  BEGIN
    IF pDirection = '12' THEN
      SELECT  obj2.obid INTO mID
      FROM    srel rel, sobj obj2
      where rel.uid2 = obj2.objuid
    ELSE
      SELECT  obj2.obid INTO mID
      FROM    srel rel, sobj obj2
      where rel.uid1 = obj2.objuid
    END IF ;

 
    If psFlow Is Not Null Then
      SELECT  ObjUID INTO mTemp
      FROM sobj
      WHERE   obid = mID;

      SELECT  ObjName INTO mRetVal
      FROM SOBJ
      WHERE   obid = mID;

      mTemp2 := SchemaGetRelObj(mTemp, 'SPFWorkflowWorkflowStep', '21');
      If mTemp2 <> psFlow Then
        mRetVal := mTemp2 || '~' || mRetVal;
      END IF;
    END

  END;
 RETURN mRetVal;
END SchemaGetRelObj;
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
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 40229110
You have to options in SQL in my opinion - you can use a Table Variable to host the record set
http://msdn.microsoft.com/en-us/library/ms188927.aspx

OR a recursive CTE may help as well:

http://msdn.microsoft.com/en-us/library/ms186243.aspx


Or think that you can use a SQL Stored Procedure instead of SQL Function and you can use Temporary Tables in those SQL objects

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
0
 

Author Comment

by:Sam OZ
ID: 40229136
Hi ,

  Can you please give me the equivalent Sql Server piece for the initial part of my Oracle function- The oracle piece given below

Function GetNewObj
(
   ParentUID       IN  VARCHAR2,
   pDirection      IN  VARCHAR2,
   psFlow       IN  VARCHAR2
   )
   RETURN VARCHAR2
IS
  mRetVal   VARCHAR2(200);
  mID     VARCHAR2(200);
  mTemp     VARCHAR2(200);
  mTemp2    VARCHAR2(200);
0
 

Author Comment

by:Sam OZ
ID: 40229140
Thanks , I got the answer , I think
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Sam OZ
ID: 40229175
Hi,
  Can you please provide the Sql Server equivalent of  my whole oracle query . I have some difficulty in making one
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40229497
working on it now...
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40229533
Should be something like code below:

create procedure GetNewObj
 (
    @ParentUID    VARCHAR(4000),
    @pDirection   VARCHAR(4000),
    @psFlow       VARCHAR(4000)
    )
    RETURN VARCHAR(4000)
AS
BEGIN
declare @mRetVal   VARCHAR(200)
            ,@mID     VARCHAR(200)
            ,@mTemp     VARCHAR(200)
            ,@mTemp2    VARCHAR(200);
 
set @mRetVal = '';

IF @pDirection = '12'
       SELECT  obj2.obid INTO @mID
       FROM    srel rel, sobj obj2
       where rel.uid2 = obj2.objuid
ELSE
       SELECT  obj2.obid INTO @mID
       FROM    srel rel, sobj obj2
       where rel.uid1 = obj2.objuid

   
If @psFlow Is Not Null
  SELECT  ObjUID INTO @mTemp
    FROM sobj
    WHERE   obid = @mID;

  SELECT  ObjName INTO @mRetVal
       FROM SOBJ
       WHERE   obid = @mID;

select @mTemp2 = SchemaGetRelObj(@mTemp, 'SPFWorkflowWorkflowStep', '21');

If @mTemp2 <> @psFlow
      set @mRetVal = @mTemp2 +'~'+@mRetVal;

RETURN @mRetVal;

END;
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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