February 21, 2011 at 1:14 am
Dear Expert :
I have create the runtime view but my problem is
in the below code
;WITH A AS (
SELECT C1,C2,C3 FROM TABLE)
SELECT * FROM A --gives us result
SELECT COUNT(1) FROM A -- gives us the invalid object name
But i need the result set and seprately the count also, but the view scope is expired first execute
is there
another way to handle?
February 21, 2011 at 1:17 am
Try this:
;WITH A AS (
SELECT C1,C2,C3 FROM TABLE)
SELECT * FROM A --gives us result
SELECT @@ROWCOUNT CountOfRows
February 21, 2011 at 1:33 am
It's great but i need the full count of the result set,
If i am using the between 1 to 10 mean the Count will 10 only..
February 21, 2011 at 2:16 am
WITH A AS (
SELECT C1,C2,C3 FROM TABLE)
SELECT *, COUNT(*) OVER ( PARTITION BY (SELECT 1)) AS TotalCount FROM A
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply