question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

How to get the count value but not the result set?

I encountered a scenario as below- declare @query nvarchar(100) set @query = 'select * from dbo.TableData' ---contains 5100 rows declare @count int select @count = COUNT(1) exec(@query) -- problem-area select @count -- should give value 5100, currently gives value=1 I thought of a way around like the code below, but then it gives me the query resultset too alongwith the count value declare @query nvarchar(100) set @query = 'select * from dbo.TableData' ---contains 5100 rows declare @count int exec(@query) select @count = @@rowcount -- my solution select @count -- should give value 5100,gives value=5100 BUT alongwith the result set of select * from dbo.TableData Can anyone help me in achieving just the count value without the extra things? Is there a way? PS: I cannot modify the @query value, it is fixed!
sql-server-2008-r2rowcount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

KenJ avatar image
KenJ answered
This is trivial if you use `select @count = count(*) from dbo.TableData` Are you really not allowed to do that? If that is off-limits, you could always wrap the query up in an outer count query: select @count = count(*) from ( select * from dbo.TableData ) as src Now you haven't modified the original query at all. I'm going to guess that, if you aren't allowed to modify the original query, that means you will also have to execute it as supplied in the variable. If you really are required to use the SQL in its variable form, you can use the same wrapper approach I outlined above with dynamic SQL: declare @countquerywrapper nvarchar(100) set @countquerywrapper = 'select @p1 = count(*) from ( ' + @query + ') as src' Of course, `EXEC()` won't return a scalar value for you, so you'll have to use `sp_executesql` instead: exec sp_executesql @countquerywrapper, @params = N'@p1 int OUTPUT', @p1 = @count OUTPUT This is an insanely complex way to get a simple count. You should probably re-evaluate the problem you're trying to solve.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Agreed. Thanks anyways!
0 Likes 0 ·