Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arielspecial
Starting Member
11 Posts |
 Posted - 2011-10-03 : 10:10:44
|
| I have a complex query with paging inside a stored procedure.It means that the query only returns like 10 out of thousands.Is there a way to return both the result table and the total rows count without running the query twice? |
|
|
gvmk27
Starting Member
44 Posts |
Posted - 2011-10-03 : 10:43:03
|
| pl post your existing query, it may help us to help you. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-03 : 12:00:28
|
| you can do it. but as suggested we need to see current code before we suggest exact solution------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arielspecial
Starting Member
11 Posts |
Posted - 2011-10-04 : 03:07:01
|
| What I'm asking is on the fundamental side. It's not a specific query. I apply paging on the sql server side.for example: select top 10 * from users where userType=1. but I also need the total count of: select count(*) from users where userType=1. So I have to do it twice! once to get the top 10 and once to get the total count with the condition on the where clause. Now, this query is very simple. What If I have a complex query? Do I have to exec it twice?Is there a known technique to deal with this situation? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-04 : 04:20:59
|
| [code]select top 10 *,count(case when userType=1 then 1 else null end) over () as totalcnt from users where userType=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
get query + rows count