?
Solved

Get Rows affected Count using SQL Query

Posted on 2011-04-21
3
Medium Priority
?
530 Views
Last Modified: 2012-05-11
Hello Experts,

I'm working on a SQL Query, where I need to count the records affetced with query i.e  I need rows count.

 
SELECT (VTC.CODE) as [CODES] , VTC as VID , isnull(EMP.TYPE,'GENERAL') as [TYPE]
FROM EMP V LEFT OUTER Join VTC
ON V.[ID] = VTC.[ID]
LEFT OUTER JOIN      dbo.DEPT AS D
ON  vtc.Code = D.Code

Open in new window


I need the count of rows affected from the above query. For that , I tried this:
 
--ResultSet

SELECT (VTC.CODE) as [CODES] , VTC as VID , isnull(EMP.TYPE,'GENERAL') as [TYPE]
FROM EMP V LEFT OUTER Join VTC
ON V.[ID] = VTC.[ID]
LEFT OUTER JOIN      dbo.DEPT AS D
ON  vtc.Code = D.Code


--Row Count
select Count(*) from (
SELECT (VTC.CODE) as [CODES] , VTC as VID , isnull(EMP.TYPE,'GENERAL') as [TYPE]
FROM EMP V LEFT OUTER Join VTC
ON V.[ID] = VTC.[ID]
LEFT OUTER JOIN      dbo.DEPT AS D
ON  vtc.Code = D.Code) as T

Open in new window


But I don't want to do it twice ,to get the count and Result set.
0
Comment
Question by:ASPDEV
  • 2
3 Comments
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 35443612
use @@ROWCOUNT:


SELECT (VTC.CODE) as [CODES] , VTC as VID , isnull(EMP.TYPE,'GENERAL') as [TYPE]
FROM EMP V LEFT OUTER Join VTC
ON V.[ID] = VTC.[ID]
LEFT OUTER JOIN      dbo.DEPT AS D
ON  vtc.Code = D.Code

select  @@ROWCOUNT as RowCount
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35443622
a better practice would be to declare a variable to hold the value in @@ROWCOUNT, since it changes with each select, update, or delete:

declare @rc int

SELECT (VTC.CODE) as [CODES] , VTC as VID , isnull(EMP.TYPE,'GENERAL') as [TYPE]
FROM EMP V LEFT OUTER Join VTC
ON V.[ID] = VTC.[ID]
LEFT OUTER JOIN      dbo.DEPT AS D
ON  vtc.Code = D.Code

select @rc = @@ROWCOUNT
select @rc as RowCount
0
 

Author Closing Comment

by:ASPDEV
ID: 35443656
Awesome!

Thanks,
ASPDEV
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question