Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-14 : 07:45:32
|
| Kelly writes "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 intSET @ifStmt = 'SELECT count(*) from ' + @tabName + ' WHERE id = ' + @pkhere's what I tried for my eval block :SELECT @ifResult = EXEC (@ifStmt)IF (@ifResult) > 0I've also tried SET @ifResult = EXEC (@ifStmt) andIF (EXEC(@ifStmt)) > 0All 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." |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-14 : 07:54:20
|
| Hi Kelly, Welcome to SQL Team!You can't do it that way, I'm afraid.You probably need to doSET @ifStmt = 'SELECT @ifResult = count(*) from ' + @tabName + ' WHERE id = @pk'EXEC sp_ExecuteSQL @ifStmt, '@ifResult int OUTPUT, @pk int??', @ifResult OUTPUT, @pk(I'm not sure what datatype @pk is, I've guessed "int")Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Evaluating an EXEC statement.