none
How can I combine these statements and stored procedures?

    问题

  • Hi folks,

    How can I combine these Stored Procedures and statements into one Stored Procedure that passes on the Document ID and then goes on to run each Stored Procedure and statements using the Document ID?

    At the moment, I am having to execute each Stored Procedure and statement line by line.

    exec dbo.usp_DCMResults_DeleteByDocID 61552096
    exec dbo.usp_DCMDocumentsLeafLevelClassifications_Delete 61552096
    DELETE FROM dbo.BatchContents WHERE DocumentID =61552096
    DELETE FROM dbo.UNMATCHEDSYNONYMS WHERE lDocumentID =61552096
    exec SP_DeleteDocCountryOfInc2 61552096
    exec dbo.usp_DocumentClassifications_DeleteByDocID 61552096 
    exec SP_DeleteDocIndices2 61552096
    exec SP_DeleteDocMarketOfListing2 61552096
    exec SP_DeleteDocCurrencies2 61552096
    exec SP_DeleteDocActuaryLinks 61552096 
    exec SP_DeleteDocCompanyLink2 61552096
    exec SP_DeleteDocLeadManagerByDocument 61552096
    exec SP_DeleteDocTransactionSize2 61552096
    exec dbo.usp_DocumentMaturityDates_DeleteByDocID 61552096
    EXEC usp_DocumentClause_DeleteByDocumentID 61552096, 'BrendanM'
    EXEC usp_DocumentFormatLinks_DeleteByDocumentID 61552096

    exec SP_DeleteDocument 61552096

    2012年7月6日 10:10

答案

  • try

    Create Proc Prc (@DocumentID numeric(10,0))
    as
    begin
    exec dbo.usp_DCMResults_DeleteByDocID @DocumentID
    exec dbo.usp_DCMDocumentsLeafLevelClassifications_Delete @DocumentID
    DELETE FROM dbo.BatchContents WHERE DocumentID =@DocumentID
    DELETE FROM dbo.UNMATCHEDSYNONYMS WHERE lDocumentID =@DocumentID
    exec SP_DeleteDocCountryOfInc2 @DocumentID
    exec dbo.usp_DocumentClassifications_DeleteByDocID @DocumentID  
    exec SP_DeleteDocIndices2 @DocumentID
    exec SP_DeleteDocMarketOfListing2 @DocumentID
    exec SP_DeleteDocCurrencies2 @DocumentID
    exec SP_DeleteDocActuaryLinks @DocumentID 
    exec SP_DeleteDocCompanyLink2 @DocumentID 
    exec SP_DeleteDocLeadManagerByDocument @DocumentID 
    exec SP_DeleteDocTransactionSize2 @DocumentID
    exec dbo.usp_DocumentMaturityDates_DeleteByDocID @DocumentID
    EXEC usp_DocumentClause_DeleteByDocumentID @DocumentID, 'BrendanM' 
    EXEC usp_DocumentFormatLinks_DeleteByDocumentID @DocumentID 
    exec SP_DeleteDocument @DocumentID
    End


    Best regards

    2012年7月6日 10:20

全部回复

  • That should be a pretty simple stuff. Create the new procedure with @DocID as parameter and use this parameter while calling the other stored procedures inside the new procedure...

    Murali Krishnan

    2012年7月6日 10:15
  • try

    Create Proc Prc (@DocumentID numeric(10,0))
    as
    begin
    exec dbo.usp_DCMResults_DeleteByDocID @DocumentID
    exec dbo.usp_DCMDocumentsLeafLevelClassifications_Delete @DocumentID
    DELETE FROM dbo.BatchContents WHERE DocumentID =@DocumentID
    DELETE FROM dbo.UNMATCHEDSYNONYMS WHERE lDocumentID =@DocumentID
    exec SP_DeleteDocCountryOfInc2 @DocumentID
    exec dbo.usp_DocumentClassifications_DeleteByDocID @DocumentID  
    exec SP_DeleteDocIndices2 @DocumentID
    exec SP_DeleteDocMarketOfListing2 @DocumentID
    exec SP_DeleteDocCurrencies2 @DocumentID
    exec SP_DeleteDocActuaryLinks @DocumentID 
    exec SP_DeleteDocCompanyLink2 @DocumentID 
    exec SP_DeleteDocLeadManagerByDocument @DocumentID 
    exec SP_DeleteDocTransactionSize2 @DocumentID
    exec dbo.usp_DocumentMaturityDates_DeleteByDocID @DocumentID
    EXEC usp_DocumentClause_DeleteByDocumentID @DocumentID, 'BrendanM' 
    EXEC usp_DocumentFormatLinks_DeleteByDocumentID @DocumentID 
    exec SP_DeleteDocument @DocumentID
    End


    Best regards

    2012年7月6日 10:20
  • Guys,

    Thanks for all your help!

    2012年7月6日 11:08