Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2003
    New Hampshire, USA

    Unanswered: Use sp_executesql to run another stored procedure

    I have two stored procedures. One inserts a row into a history table from a transaction table, and returns the Identity value, a record count, and a return code. The other updates the row in the transaction table, and returns a record count and a return code. Both use sp_executesql because their SQL is built dynamically in my Active Server Page application, and because I need some values returned (especially the Identity value on the insert). I want to set up one stored procedure that starts a transaction, runs the insert sp and retrieves its returned values, runs the update sp and retrieves its returned values, and then either commits or rolls back the transaction based on the returned values.

    My three questions are: Can this be done from a master stored procedure (or even the update sp calling the insert sp, I don't really need three sp), if so, what is the syntax I would use, and will this accomplish my goal of having this be considered one transaction?

    Both procedures as they currently exist are shown below, and they're also both in the attached file.

    CREATE PROCEDURE stp_ServicesUpdate
    @JSUID nvarchar(10),
    --update process
    @JSSet nvarchar(1000),
    --insert process
    @ArchiveInd bit,
    @ArchivedBy nvarchar(20),
    @ArchivedOn datetime,
    @HistoryIdentity int output as

    --all processes
    DECLARE @ReturnError int
    DECLARE @SQL nvarchar(2100)
    DECLARE @SQLSetError nvarchar(100)
    DECLARE @SQLSetCount nvarchar(100)
    --update process
    DECLARE @SQLUpdate nvarchar(1000)
    DECLARE @SQLWhere nvarchar(100)
    DECLARE @UpdateParmDefinitions nvarchar(200)
    DECLARE @UpdateReturnError int
    DECLARE @UpdateRecordCount int
    --insert process
    DECLARE @InsertParmDefinitions nvarchar(100)
    DECLARE @HistoryRecordCount int

    SET @ReturnError = 0
    SET @SQLUpdate = 'UPDATE Services '
    SET @SQLWhere = ' WHERE UID = ' + @JSUID
    SET @SQLSetError = ' SET @ParmUpdateReturnError = @@Error'
    SET @SQLSetCount = ' SET @ParmUpdateRecordCount = @@Rowcount'

    SET @SQL = @SQLUpdate + @JSSet + @SQLWhere + @SQLSetError + @SQLSetCount

    SET @UpdateParmDefinitions = '@ParmUpdateReturnError int output, @ParmUpdateRecordCount int output'

    EXECUTE sp_executesql
    @ParmUpdateReturnError=@UpdateReturnError output, @ParmUpdateRecordCount = @UpdateRecordCount output

    if @UpdateReturnError > 0
    SET @ReturnError = @UpdateReturnError

    RETURN @ReturnError

    CREATE PROCEDURE stp_ServicesHistoryInsert
    @ArchivedBy nvarchar(20),
    @ArchivedOn datetime,
    @JSUID nvarchar(10),
    @Identity int output,
    @RecordCount int output as

    DECLARE @ReturnError int
    DECLARE @SQLInsert nvarchar(1000)
    DECLARE @SQLSelect nvarchar(1000)
    DECLARE @SQLIdentitySet nvarchar(100)
    DECLARE @SQL nvarchar(2000)
    DECLARE @ParmDefinitions nvarchar(100)
    DECLARE @ParmValues nvarchar(200)

    SET @SQLInsert = 'INSERT INTO ServicesHistory
    (ServiceUID, SSN, ServiceDate, Counter,
    ModifiedBy, ModifiedOn, ArchivedOn, ArchivedBy) '

    SET @SQLSelect = 'SELECT UID, SSN, ServiceDate, Counter,
    ModifiedBy, ModifiedOn, ArchivedOn = @ParmArchivedOn, ArchivedBy = @ParmArchivedBy ' +
    ' FROM Services WHERE UID = ' + @JSUID

    SET @SQLIdentitySet = ' SET @ParmScopeIdentity = SCOPE_IDENTITY() '

    SET @SQL = @SQLInsert + @SQLSelect + @SQLIdentitySet
    SET @ParmDefinitions = '@ParmScopeIdentity int output, @ParmArchivedBy varchar(20), @ParmArchivedOn datetime'

    EXECUTE sp_executesql @SQL,
    @ParmScopeIdentity=@Identity output, @ParmArchivedBy = @ArchivedBy, @ParmArchivedOn = @ArchivedOn

    SET @RecordCount = @@RowCount
    SET @ReturnError = @@Error

    RETURN @ReturnError


    Thanks for your help.
    Attached Files Attached Files

Posting Permissions

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