Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: stored procedure question

    hi,
    can someone tell me how ca i call three stored procedures from another stored procedure, i want to schedule a job to run just one sp and in this one i need to call the other three?

    thanks

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi
    I am not sure if I understood your question?! :?
    Doesn't it work like that?

    exec sp_StoredProcedure1 Parameter1, Parameter2 ...
    exec sp_StoredProcedure2 Parameter1, Parameter2 ...
    exec sp_StoredProcedure3 Parameter1, Parameter2 ...

    Regards
    Kris Zywczyk

  3. #3
    Join Date
    Oct 2006
    Posts
    55
    thanks for your quick reply.
    what i want is to create a stored procedure called sp_archieve, this SP will do some cleaning for some tables and then called three other stored procedure sp_daily, sp_monthly, sp_yearly. i want it this way so i can schedule the sp_archieve.
    i think it's clear now, if you need more information just post back and i will explain more
    thanks

  4. #4
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Try this:

    Code:
    create procedure sp_archieve
    as
    begin
      set nocount on
      declare @DayOfMonth int		--Day of month when run sp_monthly
      declare @DayOfYear  datetime	--Day when run sp_yearly. Year will be ignored. 
                                    --Only month and day of the month will be used
    
      --Please setup values of @DayOfMonth and @DayOfYear
    
      set @DayOfMonth = 1  --sp_monthly will be run first day of the month
      set @DayOfYear  = convert(datetime, '2006-11-09', 121)  --sp_yearly will be run 9th November each year
    
      /*
      do some cleaning for some tables
      */
    
      --Run sp_daily procedure 
      print('Running sp_daily procedure...')
      exec sp_daily
    
      --Run sp_monthly procedure
      if datepart(day, getdate()) = @DayOfMonth
      begin
        print('Running sp_monthly procedure...')
        exec sp_monthly
      end
    
      --Run sp_yearly procedure
      if datepart(day, getdate()) = datepart(day, @DayOfYear) and
         datepart(month, getdate()) = datepart(month, @DayOfYear)
      begin
        print('Running sp_yearly procedure...')
        exec sp_yearly
      end
    end
    Regards
    Kris Zywczyk

  5. #5
    Join Date
    Oct 2006
    Posts
    55
    thanks,
    i think this way i do not need to schedule a job to be running in EM?!!
    because what i wanted to do is to create the sp_archieve and call the three SP from this Stored Procedure and last thing is to schedule a job in EM to run the sp_archieve every first day of the months.

    what do you think?!!!
    thank again for your help

  6. #6
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Why du you need sp_daily if you want to run sp_archieve every first day of the month?

    Regards
    Kris Zywczyk
    Regards
    Kris Zywczyk

  7. #7
    Join Date
    Oct 2006
    Posts
    55
    let me explain the situation:
    i had a very big table that went out of control, so what i did is create a summary database and i created 3 tables there one that gonna store the last three months data and this one represent ixtensive information for our clients, one table for the monthly data that contains data older than 3 months and newer than a year(less information included in this table), the last table is the yearly table that holds the data older than a year, after that i created the 3 SP that gonna move the data to the new tables and this way it will be easy to manage and also to boost performance, then comes the sp_archieve that i want to schedule it so it will run every 1 day of the month of course with a cutOff of the current day minus 3 months.

    i hope this will be clear enough, sorry for the misunderstanding.
    thanks

  8. #8
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    creating multiple SPs is good idea. but i think you should rather not call them from a parent SP. You can always create a job that will be run in the slack hours depending on the server usage you have. This would help to mantain the server availablity.

    Hope this helps.
    Last edited by wash; 11-09-06 at 03:40.
    In GOD we believe. Everything else we Test!

  9. #9
    Join Date
    Oct 2006
    Posts
    55
    hi wash thanks for the reply but can you explain more?!! what about server availability? i did this specially for concerving server resources. you know that running each query will need one connection so running 4 will need 4 connections, but calling the 3 SP from the Parent one will need just one connection.
    Last edited by mrjoka; 11-09-06 at 05:59.
    Peace out
    Mr joka

Posting Permissions

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