Passing info from dynamic TSQL statements

[Guest_imported]

Hi,

I am writing a stored procedure which needs to execute some dynamic TSQL SELECT statement and find out how many rows were returned.

It is written in a similar fashion to the following:

------------------------------------------------------------
...
DECLARE @sql
DECLARE @rowsfound
SELECT @sql = 'SELECT ... (etc)'

EXEC (@sql)
SELECT @rowsfound = @@rowcount
...
------------------------------------------------------------

N.B. In the situation where I have used code similar to the above, the number of rows returned is always 1 or 0.

The code works fine if I execute it from the Query Analyser. However, if I execute it from a call in my ASP program (which is ultimately where I want to use it from), then @@rowcount always seems to return 0 regardless of whether the correct answer is 1 or 0. The command used to start the stored procedure is exactly the same in both cases.

Does anyone know if this is a known "feature" in SQL or does anyone have an alternative way of obtaining the number of rows returned by the dynamic SELECT call?

Thanks in advance,
Simon.

 

[Tim1]

Hi Simon,

I am afraid that you have found one of the slight perculiarities of dynamic SQL. It only appears to stored 0 or 1 to @@rowcount, regardless of the actual number of rows that it has processed. This makes it very difficult if you actually want to report on the number of rows it has processed.

I have not managed to find a way round this problem yet....

Also, it appears to affect all DML (Inserts, Updates etc) as well as selects

Tim

 

[tlbroadbent]

I just wrote a FAQ (faq183-761) explaining how to use sp_executesql to obtain the "rows affected" count. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm