@@error and @@rowcount in single select.

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 2000 Forums

SQL Server Development (2000)

@@error and @@rowcount in single select.

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-11-02 : 22:58:25

how do i set @@error and @@rowcount to other declared varibales in a single select statement.

any link to any topis for @@error and @@rowcount to be stored in other variables

-----------------------------------------------------------------------------------------------
Ashley Rhodes

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-11-03 : 00:32:55

SELECT @Error = @@Error, @RowCount = @@RowCount

Kristen
Test

22859 Posts

Posted - 2007-11-03 : 02:38:47

"how do i set..."

The trick is, as Koji pointed out, that you have to use SELECT and not SET.

We took the view some time ago that we would use SELECT for all such variable-assignment operations, so as to be consistent.

It is also faster to do


SELECT @Start = 1,
       @End = 10,
       ...

than to use separate SET statements.

The only thing that SET has value for (that I can remember!) is that it only allows a single value to be assigned, so

SET @MyVar = Col1
FROM MyTableWithLotsOfRows

will raise an error (whereas SELECT will just give you the last value it processes). This can be a handy check if you are expecting only one result from a query.

Mind you, you could also check that @@ROWCOUNT = 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-03 : 02:48:03

quote:

SET @MyVar = Col1
FROM MyTableWithLotsOfRows

will raise an error

Thats wrong syntax

SET is ANSI standard of assinging value to a variable

More on SET vs SELECT

http://vyaskn.tripod.com/differences_between_set_and_select.htm

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 02:34:13

"Thats wrong syntax"

That's what I get for writing some syntax that I would never dream of, personally, using without checking it

What I meant was:

SET @MyVar = (SELECT Col1 FROM MyTableWithLotsOfRows)

Example:


DECLARE @MyVar varchar(8000)
SELECT @MyVar = name FROM sysobjects
PRINT 'SELECT = ' + @MyVar
GO
DECLARE @MyVar varchar(8000)
SET @MyVar = (SELECT name FROM sysobjects)
PRINT 'SET = ' + @MyVar
GO

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-04 : 09:02:29

Actually Kristen is not wrong

quote:
SET @MyVar = Col1
FROM MyTableWithLotsOfRows

will raise an error

He did state that that statement will gives an error

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 01:21:55

quote:
Originally posted by khtan

Actually Kristen is not wrong
quote:
SET @MyVar = Col1
FROM MyTableWithLotsOfRows

will raise an error

He did state that that statement will gives an error

KH
[spoiler]Time is always against us[/spoiler]

No. Read Kristen's previous reply

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 04:56:03

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

Actually Kristen is not wrong
quote:
SET @MyVar = Col1
FROM MyTableWithLotsOfRows

will raise an error

He did state that that statement will gives an error

KH
[spoiler]Time is always against us[/spoiler]

No. Read Kristen's previous reply

Madhivanan

Failing to plan is Planning to fail

No. Read khtan's previous reply

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources