Hi,
I am using Stored Procedure to return values to a recordset in ASP.
I have tested a basic Stored Procedure:
__________________________________
CREATE PROCEDURE TESTE
@UO CHAR(3)
AS
BEGIN
SELECT * FROM SGA01Q01 TD WHERE TD.UO = @UO
END
GO
__________________________________
and the asp (with connections and recordset working)
__________________________________
__________________________________
<%
sql = ""
sql = "EXEC TESTE '003'"
rsTeste.open sql, connTeste
if not rsTeste.eof then
rsTeste.MoveFirst
response.write(rsTeste("UO"))
else
response.write("Nada")
end if
%>
__________________________________
It works fine. But when I call a Stored Procedure bigger than that basic:
__________________________________
CREATE PROCEDURE SGA03S3031
@UO CHAR(3) = NULL,
@AREA_OPERACIONAL CHAR(10) = NULL,
@NIVEL CHAR(10) = NULL,
@SITUACAO CHAR(20) = NULL,
@OFERTA CHAR(10) = NULL
AS
BEGIN
SELECT
O.OFERTA,
O.SIGLA,
OFERTA_NOME = RTRIM(O.OFERTA) + ' - ' + RTRIM(O.NOME),
AREA_CONHECIMENTO = RTRIM(O.AREA_OPERACIONAL_RJ) + RTRIM(O.SUBAREA_OPERACIONAL_RJ) + ' / ' + RTRIM(AO.NOME) + ' - ' + RTRIM(SAO.NOME),
CERTIFICADO_DESCRICAO = RTRIM(TC.DESCRICAO) + ' - ' + TC.MODELO,
PAGAMENTO = CONVERT(VARCHAR,O.PRECO) + ' ou ' + RTRIM(O.PARCELAMENTO) + ' x ' + CONVERT(VARCHAR,(O.PARCELA)),
S.DURACAO,
O.PARCELA,
O.SITUACAO,
O.NIVEL,
S.PERFIL,
REQUISITOS = S.PRE_REQUISITOS,
PRE_REQUISITOS = CONVERT(VARCHAR(8000),''),
CONTEUDO = S.OBJETIVO,
COMPETENCIA = CONVERT(VARCHAR(8000),'')
INTO #SGA03S3031
FROM SGA03Q01 O, SGA01Q01 UO, SGA02Q01 AO, SGA02Q01 SAO, SGA03Q02 S, SGA03X02 TC
WHERE
( @UO IS NULL OR O.DESENVOLVIDO_NA = @UO ) AND
( @OFERTA IS NULL OR O.OFERTA = @OFERTA) AND
O.DESENVOLVIDO_NA = UO.UO AND
O.AREA_OPERACIONAL_RJ = AO.AREA_OPERACIONAL_RJ AND
AO.SUBAREA_OPERACIONAL_RJ = '' AND
O.AREA_OPERACIONAL_RJ = SAO.AREA_OPERACIONAL_RJ AND
O.SUBAREA_OPERACIONAL_RJ = SAO.SUBAREA_OPERACIONAL_RJ AND
O.OFERTA = S.SERVICO AND
S.CERTIFICADO = TC.CERTIFICADO
ORDER BY
O.AREA_OPERACIONAL_RJ,
O.SUBAREA_OPERACIONAL_RJ,
O.OFERTA
DECLARE COMPETENCIAS CURSOR FOR
SELECT O.OFERTA, CP.COMPETENCIA
FROM #SGA03S3031 O, SGA03Q06 CP
WHERE O.OFERTA = CP.SERVICO
ORDER BY O.OFERTA, CONVERT(INT,CP.ORDEM)
OPEN COMPETENCIAS
DECLARE
@C_OFERTA CHAR(10),
@Q_OFERTA CHAR(10),
@C_COMPETENCIA VARCHAR(8000)
FETCH NEXT FROM COMPETENCIAS INTO
@C_OFERTA,
@C_COMPETENCIA
SELECT @Q_OFERTA = @C_OFERTA
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@C_OFERTA <> @Q_OFERTA)
BEGIN
SELECT @Q_OFERTA = @C_OFERTA
END
UPDATE #SGA03S3031 SET
COMPETENCIA = COALESCE(COMPETENCIA,'') + CHAR(13) + (@C_COMPETENCIA)
WHERE
OFERTA = @Q_OFERTA
FETCH NEXT FROM COMPETENCIAS INTO
@C_OFERTA,
@C_COMPETENCIA
END
CLOSE COMPETENCIAS
DEALLOCATE COMPETENCIAS
DECLARE PRE_REQUISITOS CURSOR FOR
SELECT O.OFERTA, OFE.NOME
FROM #SGA03S3031 O, SGA03Q05
PR, SGA03Q01 OFE
WHERE
O.OFERTA =
PR.SERVICO AND
PR.PRE_REQUISITO = OFE.OFERTA
ORDER BY OFE.OFERTA
OPEN PRE_REQUISITOS
DECLARE
@C_PRE_REQUISITOS VARCHAR(255)
FETCH NEXT FROM PRE_REQUISITOS INTO
@C_OFERTA,
@C_PRE_REQUISITOS
SELECT @Q_OFERTA = @C_OFERTA
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@C_OFERTA <> @Q_OFERTA)
BEGIN
SELECT @Q_OFERTA = @C_OFERTA
END
UPDATE #SGA03S3031 SET
PRE_REQUISITOS = COALESCE(PRE_REQUISITOS,'') + @C_PRE_REQUISITOS
WHERE
OFERTA = @Q_OFERTA
FETCH NEXT FROM PRE_REQUISITOS INTO
@C_OFERTA,
@C_PRE_REQUISITOS
END
CLOSE PRE_REQUISITOS
DEALLOCATE PRE_REQUISITOS
SELECT
C.OFERTA,
C.SUBOFERTA,
NOME_SUBOFERTA = SO.NOME,
DURACAO_SUBOFERTA = SS.DURACAO
INTO #COMPOSICAO
FROM SGA03Q01 O
INNER JOIN SGA03Q04 C ON
O.OFERTA = C.OFERTA
INNER JOIN SGA03Q01 SO ON
C.SUBOFERTA = SO.OFERTA
INNER JOIN SGA03Q02 SS ON
SO.OFERTA = SS.SERVICO
WHERE
EXISTS ( SELECT * FROM #SGA03S3031 T WHERE T.OFERTA = O.OFERTA )
ORDER BY
O.OFERTA,
C.ORDEM,
C.SUBOFERTA
SELECT
T.*,
C.*
FROM #SGA03S3031 T
LEFT OUTER JOIN #COMPOSICAO C ON
T.OFERTA = C.OFERTA
DROP TABLE #SGA03S3031
END
GO
______________________________________
My ASP, with new string sql = "EXEC SGA03S3031 NULL, NULL, NULL, NULL, '8000'" and new response.write....
return:
______________________________________
ADODB.Recordset erro '800a0e78'
Operation is not allowed when the object is closed.
/projects/test.asp, line 25
___________________________________
So I think that the Stored Procedure isn't returning anything to my recordset.
Does anybody know about Stored Procedure with ASP?
Thanks in advance.
Hami
dBforums
>
Somebody understand Stored Procedure?
10-22-03, 10:06
Linear Mode
