CODEWITHSUNDEEP
sqlt-sql
Mardok
Mardok

Reputation: 35

Counting query result without executing query, using sp_executesql

Is it possibile to count the number of result that a query returns using sp_executesql without executing query? What I mean:

I have a procedure that gets a sql query in string. Example: 

SELECT KolumnaA FROM Users WHERE KolumnaA > 5

I would like to assign count of how many results this query will return, and store it in a variable, but I do not want to actually execute the query.

I cannot use this solution:

EXECUTE sp_executesql @sql          
SET @allCount = @@rowcount

because it returns the query result, in addition to getting the count of returned rows.

Upvotes: 2

Views: 5306

Answers (6)

Roman
Roman

Reputation: 641

Here is the result that I have found:

Getting Rowcount within sp_executesql

Basically, re-write your query as follows:

DECLARE @SQL NVARCHAR(1000)
DECLARE @Count INT
SET @SQL = 'SELECT KolumnaA FROM Users WHERE KolumnaA > 5; SELECT @Count = @@ROWCOUNT;'
DECLARE @Params NVARCHAR(100)
SET @Params = '@Count INT OUTPUT'

EXEC sp_executesql @SQL, @Params, @Count = @Count OUTPUT
PRINT @Count  --should return the number of rows

Cheers.

Upvotes: 1

David Manheim
David Manheim

Reputation: 2626

I think there are two parts to the question, which I will address.

The first part of the question is how to return row counts instead of the query results. This is done using Count(item). Using Count(1) instead of Count(KolumnaA) can be slightly faster, since it just counts the number of rows to be returned, instead of retreiving a specific column.

SELECT Count(1) FROM Users WHERE KolumnaA > 5

The second part is assigning this to a variable. If you need to use sp_executesql, you can do as follows:

Declare @sql varchar(4000)
Declare @allCount int
Set @sql = 'SELECT 1 FROM Users WHERE KolumnaA > 5'
sp_executesql(@sql)
SET @allCount = @@rowcount

Alternatively, you can try to use the sp_executesql output feature:

DECLARE @allCount int

EXEC sp_executesql
   N'@allCount = SELECT Count(1) FROM Users WHERE KolumnaA > 5',
   '@allCount int OUTPUT',
   @allCount OUTPUT

Upvotes: 2

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

In general case...

SELECT COUNT(*) FROM ( <your query> )

...which in your case can be simplified into:

SELECT COUNT(*) FROM Users WHERE KolumnaA > 5

The reason it can't be done cheaper is that there are no hidden "counters" inside the data managed by the DBMS. The DBMS won't even know the total number of rows in the table, let alone the number of rows fulfilling a criteria that is not known in advance (such as KolumnaA > 5).

So, counting requires actually finding the data, so it requires the "real" query. Fortunately, all this happens on the server and only a minute amount of data is transferred to the client (the count itself), so assuming your data is properly indexed it should be pretty fast.

Be careful about consistency though: just because the counting query returned certain count, does not mean that the "real" query will return the same number of rows (in the environment where multiple clients may be modifying the data concurrently).

Upvotes: 3

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

It will execute the query but it will return only the count not the actual result.

SELECT count(*) FROM Uzytkownicy WHERE KolumnaA > 5

Upvotes: 1

Jonathan Wood
Jonathan Wood

Reputation: 67283

Use:

SELECT COUNT(1) FROM Uzytkownicy WHERE KolumnaA > 5

Yes, it does execute a query. But it doesn't return results other than the number of rows.

Otherwise, I don't see how you can avoid returning results.

Upvotes: 2

Sachin Kainth
Sachin Kainth

Reputation: 46760

Can you somehow generate another query from the above one like this

SELECT count(*) FROM Uzytkownicy WHERE KolumnaA > 5

and then execute that?

Upvotes: 5

Related Questions