how to check whether a sql query have result or not using store procedure?

how to  check whether a sql query have result or not using store procedure?

eg. Select * from table
AStronusAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
That is correct.  Be sure, though, to save the value if you intend to test anyplace after the very next line.  For example, this code won't work as expected:

SELECT * FROM table
SET @date = GETDATE()
IF @@ROWCOUNT > 0

The SET statement resets @@ROWCOUNT (to 1)!  So, if you need the value of @@ROWCOUNT later, do this:

DECLARE @rowcount INT
SELECT * FROM table
SET @rowcount = @@rowcount
SET @date = GETDATE()
IF @rowcount > 0

By the way, the same is true for @@ERROR.

0
 
PaullkhaCommented:
@@rowcount
0
 
AStronusAuthor Commented:
that odd, i have test that before and it didn't work, now it work so weird
0
 
Scott PletcherSenior DBACommented:
Just be sure to do the save IMMEDIATELY after the SELECT (or INSERT or UPDATE or DELETE -- @@ROWCOUNT works for all) and it should work.  

Note that if you want to save BOTH @@ROWCOUNT and @@ERROR you have to save both in one line using a SELECT, like this:

DECLARE @error INT
DECLARE @rowcount INT

SELECT * FROM anyTable
SELECT @rowcount = @@ROWCOUNT, @error = @@ERROR
0
All Courses

From novice to tech pro — start learning today.