I need to count number of record return by a SQL statement. Any idea to do
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>
BBradshaw: