I have a stored procedure which I c 1

[NiteCrawlr]

I have a stored procedure which I created on SQL SERVER:

**************
CREATE PROCEDURE [SP_EM_VERIF_NOVIS]
@EMPLID char(11),
@RESULTADO numeric(11) output
AS
Set @resultado = 0
IF Exists( Select [MANAGER_ID] FROM [PS_EM_ORG_NOVIS_VW] WHERE ([MANAGER_ID] = @EMPLID))
BEGIN
SET @RESULTADO = '1'
END
ELSE
BEGIN
SET @RESULTADO = '0'
END

RETURN @resultado
************

Which lines would I have to change so I can put it on a Oracle 8 DB?

Thanks

 

[stevecal]

This ought to do:

FUNCTION SP_EM_VERIF_NOVIS( emplid IN VARCHAR2)
RETURN NUMBER;
IS
v_result NUMBER := 0;
BEGIN
Select Count(MANAGER_ID) INTO v_result FROM PS_EM_ORG_NOVIS_VW WHERE MANAGER_ID = emplid);
IF v_result >= 1 THEN
return 1;
ELSE
return 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return 0;
END SP_EM_VERIF_NOVIS;

 

[stevecal]

sorry!

The line
PS_EM_ORG_NOVIS_VW WHERE MANAGER_ID = emplid);
has a spurious ) and should read:
PS_EM_ORG_NOVIS_VW WHERE MANAGER_ID = emplid;

 

[NiteCrawlr]

I'm sorry... but I also would like to know if these are the correct way to execute this stored procedure..... I'm executing it from an ASP page

Execute 1
*********
strSQL = "DECLARE @resposta numeric (11)"
strSQL = strSQL & vbCrLf & "EXEC SP_EM_VERIF_VISAO "&subresp&", @resultado = @resposta output"
strSQL = strSQL & vbCrLf & "select @resposta as param1"
Set vervis = objconexao.execute(strSQL)
*********
Execute 2
*********
set contsub(nivel) = objconexao.execute("SP_EM_COUNT_VISAO "& subresp)
totvisao(nivel) = contsub(nivel)("totalvis&quot
*********

Thanks