asked on

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;

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harish Varghese

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'."

Alpesh Patel

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