problem with stored procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: problem with stored procedure

  1. #1
    Guillermo Guest

    problem with stored procedure


    When i try to save the stored procedure sql says: the prefix of 'nros_internos'
    column don't match with table name or alias used in query, but the columns
    names are those, somebody can help me, please?

    Create Procedure SP_BorradocCh
    @soc_codigo int
    As
    DECLARE @che_nroint int,@che_escliente bit
    DECLARE nros_internos CURSOR FOR SELECT che_nroint,che_escliente FROM
    cheque WHERE che_codsoc = @soc_codigo
    OPEN nros_internos
    FETCH NEXT FROM nros_internos
    WHILE @@fetch_status = 0
    BEGIN
    IF nros_internos.che_escliente = 1
    DELETE FROM chequecliente WHERE chc_nroint = nros_internos.che_nroint
    ELSE
    DELETE FROM chequenocliente WHERE chn_nroint = nros_internos.che_nroint
    DELETE FROM cheque WHERE che_nroint = nros_internos.che_nroint
    FETCH NEXT FROM nros_internos
    END
    CLOSE nros_internos
    DEALLOCATE nros_internos


  2. #2
    Santveer Guest

    Re: problem with stored procedure


    Guillermo,

    You need to fetch values into variable from cursor.
    Fetch from ... Into ...,...
    These variable you can use in your
    condition instead of using as cursorname.column name.

    santveer


    "Guillermo" <polonskyg@yahoo.com> wrote:
    >
    >When i try to save the stored procedure sql says: the prefix of 'nros_internos'
    >column don't match with table name or alias used in query, but the columns
    >names are those, somebody can help me, please?
    >
    >Create Procedure SP_BorradocCh
    > @soc_codigo int
    > As
    > DECLARE @che_nroint int,@che_escliente bit
    > DECLARE nros_internos CURSOR FOR SELECT che_nroint,che_escliente FROM
    >cheque WHERE che_codsoc = @soc_codigo
    > OPEN nros_internos
    > FETCH NEXT FROM nros_internos
    > WHILE @@fetch_status = 0
    > BEGIN
    > IF nros_internos.che_escliente = 1
    > DELETE FROM chequecliente WHERE chc_nroint = nros_internos.che_nroint
    > ELSE
    > DELETE FROM chequenocliente WHERE chn_nroint = nros_internos.che_nroint
    > DELETE FROM cheque WHERE che_nroint = nros_internos.che_nroint
    > FETCH NEXT FROM nros_internos
    > END
    > CLOSE nros_internos
    > DEALLOCATE nros_internos
    >



  3. #3
    kowe4ka Guest

    Re: problem with stored procedure


    Try to modify the sproc this way:
    Create Procedure SP_BorradocCh
    @soc_codigo int
    As
    DECLARE @che_nroint int,@che_escliente bit
    DECLARE nros_internos CURSOR
    FOR SELECT che_nroint,che_escliente
    FROM cheque
    WHERE che_codsoc = @soc_codigo
    OPEN nros_internos
    FETCH NEXT FROM nros_internos INTO @che_nroint ,@che_escliente
    WHILE @@fetch_status = 0
    BEGIN
    IF @che_escliente = 1
    DELETE FROM chequecliente
    WHERE chc_nroint = @che_nroint
    ELSE
    DELETE FROM chequenocliente
    WHERE chn_nroint = @che_nroint
    DELETE FROM cheque
    WHERE che_nroint = @che_nroint
    FETCH NEXT FROM nros_internos INTO che_nroint ,@che_escliente

    END
    CLOSE nros_internos
    DEALLOCATE nros_internos



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center