Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Somebody understand Stored Procedure?

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1

    Re: Somebody understand Stored Procedure?

    The error you received indicates that you are trying to access information from an object that is not open:
    ______________________________________

    ADODB.Recordset erro '800a0e78'

    Operation is not allowed when the object is closed.

    /projects/test.asp, line 25

    ___________________________________

    1.Make sure database connection ‘connTeste’ is open somewhere above your recordset:
    connTeste = CreateObject("COM", "ADODB.Connection")
    connTeste.Open(Datasource, UserName, Password, -1)
    rsTeste.open sql, connTeste

    2.If your connection is open, and it probably is, then it could be (not sure) the line in your procedure: PRE_REQUISITOS = CONVERT(VARCHAR(8000),''),

    PRE_REQUISITOS is declared and opened further below:
    -------------------------------------------------------------------------
    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
    --------------------------------------------------------------------------

    I know I didn’t solve your problem but hopefully I steered you in the right direction.

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Question

    Hi BVS,

    Thanks for helping..
    My main problem is that I can not deal with Stored Procedures very well...

    I am going to test this PRE_REQUISITOS...

    thanks.

    Hami.

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I am moving this thread to SQL PL/SQL...

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by rhs98
    I am moving this thread to SQL PL/SQL...
    oh....sorry...

    thx.





    does anybody can help me?!?! plz...

    Hamiseixas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •