Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Calling a stored procedure inside a cursor (in another stored procedure)

    So I am trying to call a stored procedure from within a cursor in another stored procedure and there are some problems. Here is the entire parent stored procedure:

    USE [GeographyDB]
    GO
    /****** Object: StoredProcedure [GEOGRAPHY].[DELETELOCATION] Script Date: 12/21/2009 08:31:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [GEOGRAPHY].[DELETELOCATION]
    -- Add the parameters for the stored procedure here
    @LocationId INT,
    @ErrorFlag BIT OUTPUT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
    BEGIN TRANSACTION

    DECLARE DeleteEventCursor CURSOR READ_ONLY FOR
    SELECT EventId FROM GEOGRAPHY.EVENT WHERE LocationId = @LocationId

    OPEN DeleteEventCursor

    DECLARE @nextEventId INT

    FETCH NEXT FROM DeleteEventCursor INTO @nextEventId

    WHILE @@FETCH_STATUS = 0 AND @ErrorFlag <> 1
    BEGIN
    EXECUTE GEOGRAPHY.DELETEEVENT @nextEventId, @ErrorFlag OUTPUT

    FETCH NEXT FROM DeleteEventCursor INTO @nextEventId
    END

    CLOSE DeleteEventCursor
    DEALLOCATE DeleteEventCursor

    IF @ErrorFlag = 0
    BEGIN
    DELETE FROM RECORD.REVIEW_AUTHOR WHERE ReviewId IN
    (SELECT LocationReviewId FROM REVIEW.LOCATIONREVIEW WHERE LocationId = @LocationId)

    DELETE FROM REVIEW.REVIEW_TAG WHERE ReviewId IN
    (SELECT LocationReviewId FROM REVIEW.LOCATIONREVIEW WHERE LocationId = @LocationId)

    DELETE FROM REVIEW.LOCATIONREVIEW WHERE LocationId = @LocationId

    DELETE FROM REVIEW.REVIEW WHERE ReviewId NOT IN
    (SELECT EventReviewId FROM REVIEW.EVENTREVIEW) AND ReviewId NOT IN
    (SELECT LocationReviewId FROM REVIEW.LOCATIONREVIEW) AND ReviewId NOT IN
    (SELECT TripReviewId FROM REVIEW.TRIPREVIEW)

    DELETE FROM GEOGRAPHY.LOCATION_GEOGRAPHY WHERE LocationId = @LocationId

    DELETE FROM GEOGRAPHY.LOCATION_LOCATION
    WHERE ChildLocationId = @LocationId OR ParentLocationId = @LocationId

    DELETE FROM GEOGRAPHY.LOCATION_MEDIA WHERE LocationId = @LocationId

    DELETE FROM GEOGRAPHY.LOCATION_ADDRESS WHERE LocationId = @LocationId

    DELETE FROM GEOGRAPHY.LOCATION_SUBCATEGORY WHERE LocationId = @LocationId

    DELETE FROM GEOGRAPHY.SEGMENT WHERE LocationAId = @LocationId OR LocationBId = @LocationId

    DELETE FROM RECORD.LOCATION_AUTHOR WHERE LocationId = @LocationId

    DELETE FROM GEOGRAPHY.LOCATION WHERE LocationId = @LocationId

    COMMIT
    END
    ELSE
    ROLLBACK
    END TRY

    BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
    ROLLBACK
    END

    SET @ErrorFlag = 1
    END CATCH
    END


    And here is the stored procedure being called within it:


    USE [GeographyDB]
    GO
    /****** Object: StoredProcedure [GEOGRAPHY].[DELETEEVENT] Script Date: 12/21/2009 08:43:22 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [GEOGRAPHY].[DELETEEVENT]
    -- Add the parameters for the stored procedure here
    @EventId INT,
    @ErrorFlag BIT OUTPUT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
    BEGIN TRANSACTION

    DELETE FROM RECORD.REVIEW_AUTHOR WHERE ReviewId IN
    (SELECT EventReviewId FROM REVIEW.EVENTREVIEW WHERE EventId = @EventId)

    DELETE FROM REVIEW.REVIEW_TAG WHERE ReviewId IN
    (SELECT EventReviewId FROM REVIEW.EVENTREVIEW WHERE EventId = @EventId)

    DELETE FROM REVIEW.EVENTREVIEW WHERE EventId = @EventId

    DELETE FROM REVIEW.REVIEW WHERE ReviewId NOT IN
    (SELECT EventReviewId FROM REVIEW.EVENTREVIEW) AND ReviewId NOT IN
    (SELECT LocationReviewId FROM REVIEW.LOCATIONREVIEW) AND ReviewId NOT IN
    (SELECT TripReviewId FROM REVIEW.TRIPREVIEW)

    DELETE FROM RECORD.EVENT_AUTHOR WHERE EventId = @EventId

    DELETE FROM GEOGRAPHY.EVENT WHERE EventId = @EventId

    COMMIT

    SET @ErrorFlag = 0
    END TRY

    BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
    ROLLBACK
    END

    SET @ErrorFlag = 1
    END CATCH
    END


    The parent stored procedure executes without raising an error, but I know that there is an error when it calls the child stored procedure, I'm just not handling the @ErrorFlag variable correctly. What I want it to do is return the output from the child stored procedure into the @ErrorFlag variable in the parent stored procedure and then test on that. Can anyone tell me how to do this correctly? Also, as I understand, stored procedures are a pretty expensive operation in SQL, so is it just a bad idea to begin with to call the child stored procedure in the cursor? I can avoid this, but it just seems like the code is cleaner this way... Any suggestions would be greatly appreciated.
    Paul Palubinski

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL Server, stored procedures are both cheap and easy from a performance perspecitve. The amount of time needed for the first call of a procedure is small, and the time needed for subsequent calls is trivial. Output parameters are slightly more expensive than procedure results, but output parameters are much more flexible and consistent.

    Cursors on the other hand are VERY expensive in terms of performance. They should only be used when absolutely necessary. If performance is an issue, I'd give serioius consideration to rewriting these procedures as a single procedure using set-based operations.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by Pat Phelan View Post
    In SQL Server, stored procedures are both cheap and easy from a performance perspecitve. The amount of time needed for the first call of a procedure is small, and the time needed for subsequent calls is trivial. Output parameters are slightly more expensive than procedure results, but output parameters are much more flexible and consistent.

    Cursors on the other hand are VERY expensive in terms of performance. They should only be used when absolutely necessary. If performance is an issue, I'd give serioius consideration to rewriting these procedures as a single procedure using set-based operations.

    -PatP
    Thanks for the response. I'm definitely trying to keep this thing lean, so I'll probably ditch the cursors and just use set-based operations like you suggested.
    Paul Palubinski

Posting Permissions

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