SQL 2000 Stored procedures.

Posted on 2006-04-07
Last Modified: 2012-06-27
Hi Experts,

I am using sql 2000 SP4,
have a databse  consist of lets say 8 tables.

lot of stored procedures on that for reporting purpose.

basically there is 1 main stored procedure.  which will call all other stored procedure.

 insert_criteria (@SD smalldatetime,@ED smalldatetime,@Dpt varchar(50),@CDTEST int output)  <----- main SP

              couple of select statements

              Insert statements

            EXEC get_ly_sd @SD,
           EXEC  get_ly_ed @ED,

                 EXEC sp_CSales @SD,@ED,@Dpt,@c
             EXEC sp_LYear  @csd,@led,@Dpt,@c
             EXEC sp_dept_wise_total_Cyear @c
             EXEC sp_dept_wise_total_lyear @c
             EXEC sp_dept_total @c
             EXEC pull_Totsales @c
              EXEC UPDATE_TOTSALES @c
             EXEC TEST_DEPTS @c
                                EXEC sp_comp_store @c      
             EXEC sp_st_total @c      

                SET @CDTEST = @c


this is what i am doing now

now based on above 1 have two basic and important questions

1 - How to i implement check lets say if any of the SP failed due to any XYZ reason it will rollback all the transactions .

   do i need to implement that check with in each SP or just in main SP which is calling these Sps. ( as mentioned above).

2 - as i am calling this main SP from

how do i control that if the result of this SP is empty. as a user is sending start date and end date from  front END .

Based on that these stored procedure will run and bring data .

it happends that user has no data for  defined dates.
so i need to display appropriate mesg on screen  
but i dont know how to control this in stored procedure that if there is no data it should return something .

specially  my  1st 4 Sps r the basic building blocks  if they dont get data all other will be empty as well.

I hope you understand the basic problems.


Question by:bsheikh
    LVL 27

    Expert Comment

    I do not know the VB side of your question - but I can say that you should check each transaction. This is how SQL Server transactions work - for example - something like this:

    UPDATE tableA
    SET status = 'good'

    UPDATE tableB
    SET amout = amount/0


    IF (@@ERROR > 0 )

    Ok - in the second update you have a divide by 0 error - the first update will still commit since there are no errors for it.

    You should create a local variable to hold the @@ERROR result (because that will change for every statement) and then check it something like

    DECLARE @err int

    SET status = 'good'

    SELECT @err = @@ERROR
    IF (@err > 0)

    This link is one of my favorite on describing how to handle errors in SQL Server :
    LVL 28

    Expert Comment

    if you dont have any commit transaction in any of the stored procedure than this will work

    begin transaction  <-----
    Call your main Stored procedure
    exec insert_criteria (datetime,smalldatetime, varchar50,@CDTEST int output)
    if @@error > 0


    Author Comment

    Hi There,
    my problem is  how to control the inner Sps

    i am pasting my main SP which contains all other Sps.

    i dont know know how to implement error control with in inner Sps and then as a whole in main SP.

    so when ever we get problem it should rollback all of that .

    please advice accordingly.

    LVL 28

    Accepted Solution

    <<so when ever we get problem it should rollback all of that . >>
    As  I suggested bilal it will rollback everthing if you put the execution of main procedure in side the transaction.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now