@@ROWCOUNT

6 Apr 2006 12:05 PM

McHenry

How can I return the number of rows returned by a select statement such as
the following one that returns zero in @Qty ?

Alter Procedure GetMonitors

(

@Qty INT OUTPUT

)

As

SELECT a.Suburb, a.State FROM

(

SELECT Suburb, State FROM Monitors

UNION
SELECT Suburb, State FROM Properties

WHERE Delisted Is Null

) a

GROUP BY Suburb, State

SET @Qty = @@ROWCOUNT

return

Thanks in advance...

6 Apr 2006 12:24 PM

Tibor Karaszi

Below work just fine for me.... Did you define the parameter as out when you call the proc?

USE pubs
GO

ALTER PROC p
@qty int OUT
AS
SELECT au_lname, au_fname FROM authors
SET @qty = @@ROWCOUNT
GO

--Prove that it works:
DECLARE @rows int
EXEC p @qty = @rows OUT
PRINT @rows

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"McHenry" <mche***@mchenry.com> wrote in message
news:44350412$0$20647$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
> How can I return the number of rows returned by a select statement such as the following one that
> returns zero in @Qty ?
>
> Alter Procedure GetMonitors
>
> (
>
> @Qty INT OUTPUT
>
> )
>
> As
>
> SELECT a.Suburb, a.State FROM
>
> (
>
> SELECT Suburb, State FROM Monitors
>
> UNION
> SELECT Suburb, State FROM Properties
>
> WHERE Delisted Is Null
>
> ) a
>
> GROUP BY Suburb, State
>
> SET @Qty = @@ROWCOUNT
>
> return
>
>
>
> Thanks in advance...
>
>

6 Apr 2006 12:57 PM

McHenry

Thanks... my mistake !

Show quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:OMCL8TXWGHA.1564@TK2MSFTNGP03.phx.gbl...
> Below work just fine for me.... Did you define the parameter as out when
> you call the proc?
>
> USE pubs
> GO
>
> ALTER PROC p
> @qty int OUT
> AS
> SELECT au_lname, au_fname FROM authors
> SET @qty = @@ROWCOUNT
> GO
>
> --Prove that it works:
> DECLARE @rows int
> EXEC p @qty = @rows OUT
> PRINT @rows
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "McHenry" <mche***@mchenry.com> wrote in message
> news:44350412$0$20647$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>> How can I return the number of rows returned by a select statement such
>> as the following one that returns zero in @Qty ?
>>
>> Alter Procedure GetMonitors
>>
>> (
>>
>> @Qty INT OUTPUT
>>
>> )
>>
>> As
>>
>> SELECT a.Suburb, a.State FROM
>>
>> (
>>
>> SELECT Suburb, State FROM Monitors
>>
>> UNION
>> SELECT Suburb, State FROM Properties
>>
>> WHERE Delisted Is Null
>>
>> ) a
>>
>> GROUP BY Suburb, State
>>
>> SET @Qty = @@ROWCOUNT
>>
>> return
>>
>>
>>
>> Thanks in advance...
>>
>>
>