Post reply

Dynamic SQL - evaluate EXEC results?

  • December 13, 2005 at 4:38 pm

    #96070

    I'm writing a trigger that dynamically alters an audit/storage table from a common name-value-pair insert table. I need to use dynamic sql to execute most of the statements since I don't know at compile time which target table will be modified. I seem to have everything working except the ability to evaluate the results of an EXEC statement. The statement I need starts off my IF block by checking to see if rows exist in the table and helps me determine if it's an update or an insert. Here's what I've tried:

    here's my sql string:

    @ifStmt nvarchar(500)

    @ifResult int

    SET @ifStmt = 'SELECT count(*) from ' + @tabName + ' WHERE id = ' + @pk

    here's what I tried for my eval block :

    SELECT @ifResult = EXEC (@ifStmt)

    IF (@ifResult) > 0

    I've also tried

    SET @ifResult = EXEC (@ifStmt)

    and

    IF (EXEC(@ifStmt)) > 0

    All give me an 'Incorrect syntax near the keyword EXEC'. Any ideas would be most appreciated. I can include the entire text of the trigger, it's just rather messy.

  • December 13, 2005 at 4:52 pm

    #609351

    Use @@ERROR AND @@ROWCOUNT to evaluate:

    --sample failure:

    exec ('insert into sysobjects select ''abc''' )

    select @@error, @@rowcount

    --sample success:

    exec ('declare @a int select @a=1 ' )

    select @@error, @@rowcount

     

  • December 13, 2005 at 4:56 pm

    #609353

    Thanks!  That worked - my T-SQL is rusty - I forgot about using the system vars...

  • December 13, 2005 at 5:04 pm

    #609356

    Since you are using an aggregate function, the @@rowcount will always return one record. Alternatively, use sp_executesql stored proc with an OUTPUT parameter to return the number of records based on your SQL statement. Refer to BOL for more info.

  • December 27, 2005 at 4:45 pm

    #611630

    Thanks Paul - tyruns out I do need to capture an output value from my execution statement, however, it doesn't appear that you can specify an out parameter for sp_executesql, unless I'm reading it and BOL incorrectly - do you happen to have an example?

    Kelly

  • December 27, 2005 at 4:57 pm

    #611631

    Looks like I should have done more research before posting - here's an example for anyone else with the same problem to solve:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;262499

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply