Solved

Sql Server Function with  temporary variables inside

Posted on 2014-07-30
6
362 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
  • 3
  • 3
6 Comments
 
LVL 39

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 39

Expert Comment

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AD and SQL Server 2016 2 29
SQL Syntax Grouping Sum question 7 27
sql 2016 data tools breakdown.. 1 15
Oracle Nested table uses ? 2 35
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

831 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