How to find rows returned by EXEC statement.

[jdkulkarni]

Friends, Can somebody tell me how to find number of rows returned by EXEC statement? I have following example. SET @Sql = 'SELECT COUNT(*) FROM Table where columnA = 10' EXEC (@Sql). Where can I get count? Jayant

[andyharman]

Hi Jayant You would only the "EXEC" if your SQL statement is dynamic. The following code would allow you to get the count for your above static SQL:

DECLARE @Rows INT
SELECT @Rows = COUNT(*) FROM Table where ColumnA = 10

If you want to search for different values then use:

DECLARE @Rows INT, @MySearch INT
SET @MySearch = 9
SELECT @Rows = COUNT(*) FROM Table where ColumnA = @MySearch

If your SQL truely needs to be dynamic then use:

DECLARE @Rows INT, @SQL VARCHAR(500)
 
--Create temporary table to hold results.
CREATE TABLE #temp_result (Rows INT NOT NULL)
 
--Construct and execute dynamic SQL to be used (would normally be more complex).
SET @SQL = 'INSERT INTO #temp_result (Rows)
      SELECT COUNT(*) FROM Table where columnA < 10'
EXEC (@SQL)
 
--Get results.
SELECT @Rows = Rows FROM #temp_result

Hope that helps. Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

[jdkulkarni]

Hey Andy, Thanks a lot for gr8 answer.. It worked well. I voted 5... Jayant