Solved

Sql Server Function with  temporary variables inside

Posted on 2014-07-30
6
365 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 46
SQL Sum of items in two tables not equal. 5 45
Help needed in sql query 4 26
Find special characters using tSQL 6 15
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

761 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