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
精彩评论