@@ROWCOUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

@@ROWCOUNT

Author

Topic

timmemac
Starting Member

6 Posts

Posted - 2010-06-08 : 10:28:04

Hi,

I have a select statement in Stored Proc.I am using @@ROWCOUNT to check if the select statement fetched records.Is this a good approach?.Pls share if you have a better way.

Regards
Jane

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:31:17

quote:
Originally posted by timmemac

Hi,

I have a select statement in Stored Proc.I am using @@ROWCOUNT to check if the select statement fetched records.Is this a good approach?.Pls share if you have a better way.

Regards
Jane

It depends on what you do after selecting it
What are you trying to do?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:32:29

It depends on what you are trying to do.
Also it is important to have no other selects between the select and the use of @@rowcount but I think you already know that.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:32:53

OMG

No, you're never too old to Yak'n'Roll if you're too young to die.

timmemac
Starting Member

6 Posts

Posted - 2010-06-08 : 10:34:16

I am checking if a record exists.

Note: select statement will run for a set of input parameters in the where clause

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:35:18

Then you should better use
IF EXISTS(select ...)

Which gives false or true

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:38:11

quote:
Originally posted by timmemac

I am checking if a record exists.

Note: select statement will run for a set of input parameters in the where clause

You should use

If exists(select * from ........)

instead of

select * from ........
IF @@ROWCOUNT>0
.
.

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:39:06

Ha!

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:43:43

quote:
Originally posted by webfred

Ha!

No, you're never too old to Yak'n'Roll if you're too young to die.

We often post similar replies

Madhivanan

Failing to plan is Planning to fail

timmemac
Starting Member

6 Posts

Posted - 2010-06-08 : 10:57:06

Hi,

Getting error when compiling

CREATE PROCEDURE [dbo].[login]
@userid AS VARCHAR(10),
@pass AS VARCHAR(10),
@pid AS VARCHAR(12) OUTPUT,
@sid AS INT OUTPUT,
@Cnt AS INT OUTPUT
AS
BEGIN
SET @Cnt = 0
IF EXISTS(SELECT @pid = v.pid ,@sid = v.sid FROM ULogin v WHERE v.logon = @userid AND v.pass = @pswd)

BEGIN
SET @Cnt = 1
END
END

Regards

Jane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:02:31

Change to

IF EXISTS(SELECT * FROM Usr_Showcase_Vendor_Login v WHERE v.logon = @userid AND v.pass = @pass)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-08 : 11:09:26

If you are trying to assign some variables from a select and the test if there is data, simply check to see if the variable is NULL. For example:

SET @PID = NULL

SELECT @pid = v.pid ,@sid = v.sid 
FROM Usr_Showcase_Vendor_Login v 
WHERE v.logon = @userid AND v.pass = @pswd

IF @PID IS NOT NULL
BEGIN 
	SET @Cnt = 1 
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources