Re: Running multiple sql statements from one Stored procedure

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 21:43:48 +0200

On Tue, 21 Sep 2004 12:37:18 -0400, Rob wrote:

>I created each in a procedure
>but would like to combind into one stored procedure, not sure if this can be
>done.

Hi Rob,

Sure it can be done. There several options available:

1. Keep the current procs, add one wrapper proc that calls them all:

CREATE PROC Combined
AS
 EXEC Proc1
 EXEC Proc2
 EXEC Proc3
go

2. Create one proc that contains the code from all other procs:

CREATE PROC Combined
AS
 Code from Proc1,
  you can use copy and paste
  to get it all in
 Code from Proc2,
  you can use copy and paste
  to get it all in
 Code from Proc3,
  you can use copy and paste
  to get it all in
go

In both cases, you might want to add error handling. You might also want
to enclose everything in a transaction, so you won't be left with partial
results if anything goes wrong. (Explanation: without transaction, if
Proc3 goes wrong results from Proc3 are rolled back, but results from
Proc2 and Proc1 will remain in the database. With transaction, all changes
from all procs will be rolled back if anything goes wrong).

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: DBD::Sybase and auto commit
    ... >> The first thing you can do in your stored proc is to use a ... >> create proc foo ... and not the rest of the transaction. ...
    (perl.dbi.users)
  • Re: Global Temp Table & Multiple Users
    ... what is happening is this a dynamic SQL ... The inner proc generates a portion of the SQL, ... >within a transaction, and at the start of the ... I want to queue up the execution if multiple ...
    (microsoft.public.sqlserver.server)
  • RE: SQL Port: using of the ROLLBACK
    ... As you stated the adapter is trying to Commit ... so you could start your own transaction in the Stored ... transaction before starting a new one in the proc. ... property on your Receive Location or Send Port to disable automatic ...
    (microsoft.public.biztalk.general)
  • Re: coding for no blocking or deadlocks.
    ... you should be able to write re-usable code and the ... tables in a transaction without causeing blocking. ... > proc 1 ... > This causes blocking, I need to do these things without the blocking. ...
    (microsoft.public.sqlserver.programming)