SQL 2000 Stored procedures.

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 VB.net

how do i control that if the result of this SP is empty. as a user is sending start date and end date from Vb.net  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.

Regards,
bilal
 
 


bsheikhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptjcbCommented:
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:
BEGIN TRANS

UPDATE tableA
SET status = 'good'

UPDATE tableB
SET amout = amount/0

SELECT @@ERROR

IF (@@ERROR > 0 )
ROLLBACK
ELSE
COMMIT

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
BEGIN TRAN

UPDATE TABLEA
SET status = 'good'

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

This link is one of my favorite on describing how to handle errors in SQL Server : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00f15.asp
0
imran_fastCommented:
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
rollback
else
commit

0
bsheikhAuthor Commented:
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.



Regards,
Bilal
0
imran_fastCommented:
<<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.

Regards,
Imran
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.