Sql Server Function with temporary variables inside

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;
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sam OZAuthor Commented:
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
Sam OZAuthor Commented:
Thanks , I got the answer , I think
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sam OZAuthor Commented:
Hi,
  Can you please provide the Sql Server equivalent of  my whole oracle query . I have some difficulty in making one
0
lcohanDatabase AnalystCommented:
working on it now...
0
lcohanDatabase AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.