?
Solved

SQL 2000 Stored procedures.

Posted on 2006-04-07
4
Medium Priority
?
174 Views
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 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
 
 


0
Comment
Question by:bsheikh
  • 2
4 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16401707
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16416855
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
 

Author Comment

by:bsheikh
ID: 16417120
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
 
LVL 28

Accepted Solution

by:
imran_fast earned 750 total points
ID: 16424872
<<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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.
Suggested Courses

862 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