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