How do I count the return of selected rows in SQL Server?

Question

I have one select query declared in a variable. Now I want to check if the query returns 0 rows then I have to go with other conditions. I am using SQL SERVER 2012. How do I check the variable if it contains 0 rows. Kindly suggest

  set @sql = 'select tsf.StaffId,tsf.FullName,tsf.[Address],tsf.PhoneNo,tsf.Email,tsf.Gender,tsf.MobileNo1,tsf.MobileNo2, tsf.Post,                         tsf.Salary,ts.AdvanceSalary,ts.[Description],ts.[Month] from Tbl_Salary ts
                        JOIN Tbl_Staff tsf on ts.StaffId = tsf.StaffId'
                        If @CategoryId is not null        
                        set @sql = @sql + ' where tsf.StaffId='''+cast(@CategoryId as varchar(10))+''''
                        If @MonthName is not null
                        set @sql = @sql +' and ts.Month='''+cast(@MonthName as varchar(50))+''''
                        If @Year is not null
                        set @sql = @sql +' and ts.Year='''+cast(@Year as varchar(50))+''''
            exec(@sql)

The variable contains absolutely no rows. It's just a string. On the other hand it contains a *lot* of SQL injection vulnerabilities — Panagiotis Kanavos, Sep 22 '16 at 09:19

Jamiec · Accepted Answer · 2016-09-22T09:32:11.300

You could simply change your query to

select COUNT(*)
from Tbl_Machine where MachineId = @MachineId

There is no point transmitting all those rows of data if you're only interested in the count.

Also, almost all database libraries return a property with the number of rows when you execute a query - you might like to look that up for whatever library you're using.

Looking at your update there is absolutely no need for dynamic sql here - and using it will destroy your performance. You simply have some conditional logic

select tsf.StaffId,tsf.FullName,tsf.   [Address],tsf.PhoneNo,tsf.Email,tsf.Gender,tsf.MobileNo1,tsf.MobileNo2, tsf.Post,                         tsf.Salary,ts.AdvanceSalary,ts.[Description],ts.[Month] 
from Tbl_Salary ts
JOIN Tbl_Staff tsf on ts.StaffId = tsf.StaffId
WHERE (@CategoryId IS NULL OR tsf.StaffId=@CategoryID)
AND (@MonthName IS NULL OR ts.Month=@MonthName)
AND (@Year IS NULL OR and ts.Year=@Year)

For T-SQL it's `@@ROWCOUNT`. Although the OP should rethink the entire problem - "conditional" queries are a strong smell. Stored procedures that execute one or another statement based on the results of sume query are another. Eg, attemptint to select `INSERT` or `UPDATE` based on the existence of data, when just running both statements would be faster due to indexing and optimization — Panagiotis Kanavos, Sep 22 '16 at 09:27

score 1 · Answer 2 · edited Sep 22 '16 at 11:43

1

You should also check @@rowcount, that

Returns the number of rows affected by the last statement.

So, after your query just add

  IF @@ROWCOUNT =0
  BEGIN
  --do something
  END

Be sure to also read @@ROWCOUNT (Transact-SQL)

edited Sep 22 '16 at 11:43

Jamiec

133,658
13
134
193

answered Sep 22 '16 at 09:37

wilk16

11
3

score 0 · Answer 3 · edited Sep 22 '16 at 08:45

0

if not exists
(select MachineId, MachineName, PurchasedDate, CompanyName, ModelNo, Amount, InsuranceCom,
 MnthlyInstallAmt, TotalPaid, MnthlyInstallDate from Tbl_Machine 
 where MachineId = @MachineId)
begin
--returned 0 rows
--go with other conditions
end

edited Sep 22 '16 at 08:45

Reporter

3,897
5
33
47

answered Sep 22 '16 at 08:42

TheGameiswar

27,855
8
56
94

Thanks for the response. Are there any ideas for checking with the declare variables becaues I have 3 to 4 condition for checking in where clause. – user6672226 Sep 22 '16 at 08:53
I am not sure,i understood your requirement totally,Can you please update question with what was obstructing from all answers here – TheGameiswar Sep 22 '16 at 08:54
@TheGameiswar He said that his select statement is in a variable, I'm assuming he wants to count the results for this variable when it is executed - e.g. EXEC (@sql) – Iztoksson Sep 22 '16 at 09:11

ajay panchal · Answer 4 · 2016-09-22T09:09:37.593

0

set @sql = "select count(temp_all.MachineId) AS 'Total_count' from 
           (select MachineId, MachineName, PurchasedDate, CompanyName,  ModelNo, 
            Amount, InsuranceCom, MnthlyInstallAmt, TotalPaid, MnthlyInstallDate
            from Tbl_Machine where MachineId = @MachineId) AS temp_all"

edited Sep 22 '16 at 09:09

answered Sep 22 '16 at 08:49

ajay panchal

145
1
11

How do I count the return of selected rows in SQL Server?

4 Answers4