Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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
0
AStronus
Asked:
AStronus
  • 2
1 Solution
 
PaullkhaCommented:
@@rowcount
0
 
Scott PletcherSenior 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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now