Error while converting function from oracle to SQL Server
- by sss
Hi,
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
