Error while converting function from oracle to SQL Server

I am migrating a function from Oracle to SQL Server 2008. This function raises SELECT statements included within a function cannot return data to a client as error. How can I solve this problem?

Original PLSQL Code

CREATE OR REPLACE function f_birim_cevrim_katsayi (p_ID_MAMUL in number, p_ID_BIRIMDEN in number, p_ID_BIRIME in number) 
return number
is

v_katsayi number;

begin

v_katsayi:=0;

if p_ID_BIRIMDEN!=p_ID_BIRIME then

    for c in (
         select * from CR_BIRIM_CEVRIM
         where ID_MAMUL = p_ID_MAMUL
         and (
         (ID_BIRIM = p_ID_BIRIMDEN and ID_BIRIM2 = p_ID_BIRIME)
         OR ( ID_BIRIM2 = p_ID_BIRIMDEN and ID_BIRIM = p_ID_BIRIME) )
         and VALID = 1) 
         loop

         if c.ID_BIRIM=p_ID_BIRIMDEN then
            v_katsayi:=c.MT_ORAN;
         else
            v_katsayi:=1/c.MT_ORAN;
         end if;
    end loop;
else
    v_katsayi:=1;
end if;

return round(v_katsayi,10);

exception 
when others then 
return 0;

end;

T-SQL code:

If Exists ( SELECT name 
                FROM sysobjects  
                WHERE name = 'f_birim_cevrim_katsayi'
                AND type = 'FN')
        DROP FUNCTION f_birim_cevrim_katsayi
    GO 
    CREATE FUNCTION f_birim_cevrim_katsayi
    (
        @p_ID_MAMUL                               FLOAT ,
        @p_ID_BIRIMDEN                            FLOAT ,
        @p_ID_BIRIME                              FLOAT 
    )
    RETURNS float 
    AS 
        BEGIN


            DECLARE @adv_error INT


            DECLARE @v_katsayi                                FLOAT 
            SELECT @v_katsayi  = 0 
            IF @p_ID_BIRIMDEN != @p_ID_BIRIME 
            BEGIN 


                DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
              开发者_如何转开发  SELECT *
                FROM  CR_BIRIM_CEVRIM 
                WHERE    ID_MAMUL  = @p_ID_MAMUL
                 AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM2  = @p_ID_BIRIME)
                 OR (ID_BIRIM2  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM  = @p_ID_BIRIME))
                 AND    VALID  = 1

                OPEN cursor_for_inline_select1 

                FETCH NEXT FROM  cursor_for_inline_select1  
                WHILE (@@FETCH_STATUS <> -1)
                BEGIN
                    IF   c.ID_BIRIM = @p_ID_BIRIMDEN 
                    BEGIN 
                        SELECT @v_katsayi  = c.MT_ORAN 
                    END
                    ELSE
                    BEGIN 
                        SELECT @v_katsayi  = 1/c.MT_ORAN 
                    END

                END
                CLOSE cursor_for_inline_select1
                DEALLOCATE cursor_for_inline_select1

            END
            ELSE
            BEGIN 
                SELECT @v_katsayi  = 1 
            END


     DEALLOCATE cursor_for_inline_select1
            return ROUND(@v_katsayi, 10) 
            GOTO ExitLabel1
            Exception1:

                BEGIN

     DEALLOCATE cursor_for_inline_select1
                return 0 
                            END
            ExitLabel1:

            return ROUND(@v_katsayi, 10) 

        END


    GO

---

From CREATE FUNCTION

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed

This means this is wrong

DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
            SELECT *
            FROM  CR_BIRIM_CEVRIM 
            WHERE    ID_MAMUL  = @p_ID_MAMUL
             AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
             AND    ID_BIRIM2  = @p_ID_BIRIME)
             OR (ID_BIRIM2  = @p_ID_BIRIMDEN
             AND    ID_BIRIM  = @p_ID_BIRIME))
             AND    VALID  = 1

You are not assigning the CURSOR output to local variables.

Please describe what you want to do with sample data: I'm sure this is a simple SELECT with a CASE

Edit: You don't need a CURSOR unless my pl/sql is way off

CREATE FUNCTION f_birim_cevrim_katsayi (
    @p_ID_MAMUL                               FLOAT ,
    @p_ID_BIRIMDEN                            FLOAT ,
    @p_ID_BIRIME                              FLOAT 
)
RETURNS float 
AS 
BEGIN
    DECLARE @v_katsayi FLOAT;
    IF @p_ID_BIRIMDEN != @p_ID_BIRIME 
    BEGIN 
        SELECT
             @v_katsayi =  CASE
                 WHEN ID_BIRIM = @p_ID_BIRIMDEN THEN c.MT_ORAN 
                 ELSE 1/c.MT_ORAN 
             END
        FROM 
             CR_BIRIM_CEVRIM c
        WHERE
             ID_MAMUL  = @p_ID_MAMUL
             AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
             AND    ID_BIRIM2  = @p_ID_BIRIME)
             OR (ID_BIRIM2  = @p_ID_BIRIMDEN
             AND    ID_BIRIM  = @p_ID_BIRIME))
             AND    VALID  = 1;
    END
    RETURN ROUND(ISNULL(@v_katsayi, 1), 10);
END
GO

---

Try changing these two lines

SELECT @v_katsayi  = 0 

SELECT @v_katsayi  = 1 

to

SET @v_katsayi  = 0 

SET @v_katsayi  = 1 

Missed a couple others. Anywhere you are using SELECT to simply set a variable value, change to SET instead...

Also: change your cursor to grab the field and put it into a variable

DECLARE @theID FLOAT
DECLARE @theORAN FLOAT

DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
                SELECT id_birim,MT_ORAN  -- Fields name instead of *
                FROM  CR_BIRIM_CEVRIM 
                WHERE    ID_MAMUL  = @p_ID_MAMUL
                 AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM2  = @p_ID_BIRIME)
                 OR (ID_BIRIM2  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM  = @p_ID_BIRIME))
                 AND    VALID  = 1

                OPEN cursor_for_inline_select1 
                -- Put field value into variable
                FETCH NEXT FROM  cursor_for_inline_select1  INTO @theID,@theORan
                WHILE (@@FETCH_STATUS <> -1)
                BEGIN
                    -- Compare variable rather than field directly
                    IF   @theID = @p_ID_BIRIMDEN 
                    BEGIN 
                        SELECT @v_katsayi  = @theORan 
                    END
                    ELSE
                    BEGIN 
                        SELECT @v_katsayi  = 1/@theORan 
                    END

                END
                CLOSE cursor_for_inline_select1
                DEALLOCATE cursor_for_inline_select1