Get equivalent Sql Server Query for given Oracle Query

Hi Experts,

    Can  you please help me to get the  Equivalent for the following Oracle function

 
create or replace
Function GetOBJ
(
   ParentID       IN  VARCHAR2,
   DefnID          IN  VARCHAR2,
   Dirctn       IN  VARCHAR2 DEFAULT '12'
)
   RETURN VARCHAR2
IS
   mRetVal    VARCHAR2(200);
BEGIN
  mRetVal := '';
  BEGIN
    IF Dirctn = '12' THEN
      SELECT  obj2.ObjName INTO mRetVal
      FROM    datarel rel, dataobj obj2
      WHERE Rel.uid1 = ParentID
              AND rel.uid2 = obj2.objuid
              AND rel.defuid = DefnID;
             
    Else
      SELECT  obj2.ObjName INTO mRetVal
      FROM    datarel rel, dataobj obj2
      WHERE Rel.uid2 = ParentID
              AND rel.uid1 = obj2.objuid
              AND rel.defuid = DefnID;
    END IF;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      mRetVal := '**>1 Child**';
    WHEN NO_DATA_FOUND THEN
      mRetVal := '';
    WHEN OTHERS THEN
      mRetVal := '!!!ERR!!!';
  END;
  RETURN mRetVal;
END GetObj;
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.

Harish VargheseProject LeaderCommented:
ALTER Function dbo.GetOBJ
 (
    @ParentID VARCHAR(30),
    @DefnID   VARCHAR(30),
    @Dirctn   VARCHAR(30) = '12'
 )
RETURNS VARCHAR(100)
AS 
BEGIN 
	Declare @mRetVal    VARCHAR(200) 
	SET @mRetVal = '' 
	
	IF @Dirctn = '12'  
		SELECT  @mRetVal = obj2.ObjName 
		FROM    datarel rel, dataobj obj2 
		WHERE Rel.uid1 = ParentID 
		AND rel.uid2 = obj2.objuid 
		AND rel.defuid = DefnID 
	Else 
		SELECT  @mRetVal = obj2.ObjName  
		FROM    datarel rel, dataobj obj2 
		WHERE Rel.uid2 = ParentID 
		AND rel.uid1 = obj2.objuid 
		AND rel.defuid = DefnID 

	RETURN @mRetVal 
END 

Open in new window

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
Harish VargheseProject LeaderCommented:
Few points about above function:
1. There is no CREATE OR REPLACE option in SQL Server. You need to use CREATE first time, and ALTER thereafter.
2. SQL Server does not allow TRY... CATCH block inside a function. You need to capture the error outside the function.
3. Above SELECT statement will not throw an error if there are more than one record matching the condition; instead it assign the last fetched value to the variable.

Below is an example that shows how to catch exception in SQL Server:
begin try
	exec ('Select * from from table')
end try
begin catch
	SELECT  ERROR_MESSAGE()
end catch

Open in new window

Above code will show "Incorrect syntax near the keyword 'from'."
0
Alpesh PatelAssistant ConsultantCommented:
CREATE Function dbo.GetOBJ
 (
    @ParentID VARCHAR(50),
    @DefnID   VARCHAR(50),
    @Dirctn   VARCHAR(50) = '12'
 )
RETURNS VARCHAR(200)
AS 
begin 
	declare @MRETVAL    varchar(200) = '',
          @mRetCount  int
	
	if @DIRCTN = '12'  begin
    SELECT  @mRetCount = count(1)
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid1 = @ParentID 
		and REL.UID2 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
    
    if @MRETCOUNT > 1
      set @MRETVAL = '**>1 Child**'
    if @MRETCOUNT = 0
      set @MRETVAL = '!!!ERR!!!'
    
		SELECT  @mRetVal = obj2.ObjName 
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid1 = @ParentID 
		and REL.UID2 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
	END
  else 
  BEGIN
    
    SELECT  @mRetCount = count(1)
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid2 = @ParentID 
		and REL.UID1 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
    
    if @MRETCOUNT > 1
      set @MRETVAL = '**>1 Child**'
    if @MRETCOUNT = 0
      set @MRETVAL = '!!!ERR!!!'

		SELECT  @mRetVal = obj2.ObjName  
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid2 = @ParentID 
		and REL.UID1 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
  END

	return @MRETVAL 
END 

Open in new window

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.