Solved

Sql Server Function with  temporary variables inside

Posted on 2014-07-30
6
369 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
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.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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