Storing results into variables. 2

[Averno]

I need to manage the number of rows affected by a query.
If I run this script on my database:
SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
( SELECT *
FROM ERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2 )

the query analyzer gives me back the number of lines affected:

-----------
4,00

(1 row(s) affected)

I need to manage this amount (4,00), so I applied this modification:

DECLARE @NUM_ROWS INT

SET @NUM_ROWS = (SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
( SELECT *
FROM ERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2 )
)

but it doesn't work!! the SRV returns me this:
Server: Msg 107, Level 16, State 3, Line 3
The column prefix 'TABLE_NAME' does not match with a table name or alias name used in the query.

Do you know another way to store the number of rows affected? Thanks a lot, bye.
Averno, Italy.

 

[Andel]

The number of rows affected from your last query is stored in @@rowcount variable. So, if you run your first query and it returns 4,000.

SELECT @@ROWCOUNT will return 4,000

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

The number of rows affected by the first query is 1 so @@rowcount will only contain 1 not the number you want.

Try this.

DECLARE @NUM_ROWS INT

SELECT @NUM_ROWS=COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT * FROM ERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1=TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2=TABLE_NAME.FIELD_2)

 

[Averno]

Just to add I already knew the @@rowcount, but, as tlbroadbent said, in my case it would contain 1, not 4. Thanks to both Andel and tlbroadbent for helping. (the last suggestion worked!!) Thanks a lot, bye.
Averno, Italy.

 

[JNC73]

Hi Averno,

The best way to get the result into a variable is

DECLARE @NUM_ROWS INT

SELECT @NUM_ROWS = COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
( ETCETERA....)


This will give you the result you need.


JNC73