>Hi,
>In my trigger, I use dynamic sql to check for record existence in other table before do actual update.
>
>Currently, I am using @@ROWCOUNT to check rows after run the dynamic SQL using EXEC(). However, it affect RecordsAffected property in ADO.NET. I also tried to use "SELECT @row_count = COUNT(*) ... ", but it hit error, seem like @row_count in not accessible in dynamic sql scope.
>
>Any workaround?
>
>Thank you
Maybe there is better solution that using dynamic SQL, but nobody could tell you when we didn't know your code.
try this:
........
CREATE TABLE #test (nCont int)
'INSERT INTO #test
SELECT COUNT(*) ........'
EXEC (.....)
DECLARE @row_count
SELECT @row_count = #Test.nCount FROM #Test
DROP TABLE #Test
.....
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.