@@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

General SQL Server Forums

New to SQL Server Programming

@@ROWCOUNT

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-19 : 15:59:32

why in both the occassion it is printing the same out put, could you plz tell me how to check

SELECT A FROM tab WHERE 1=1 then I want to print
'PERFORM OTHER ACTION'
SELECT A FROM tab WHERE 1=2 then I want to print
'no data found'

Actually I am trying to implement when_no_data found exception of oracle in to sqlserver, for me when no records is selected then no_data_found else preform other action..


CREATE TABLE tab ( A SMALLINT)
INSERT INTO tab VALUES(1)

DECLARE 
@A NUMERIC
BEGIN
   SELECT @A = (SELECT A FROM tab WHERE 1=1)
   IF @@ROWCOUNT = 0
   BEGIN 
    PRINT 'no data found'
   END
 PRINT 'PERFORM OTHER ACTION'
END
 
output--> PERFORM OTHER ACTION


DECLARE 
@A NUMERIC
BEGIN
   SELECT @A = (SELECT A FROM tab WHERE 1=2)
   IF @@ROWCOUNT = 0
   BEGIN 
    PRINT 'no data found'
   END
 PRINT 'PERFORM OTHER ACTION'
END
 
output--> PERFORM OTHER ACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 16:20:42

Remove the assignment. For example:

BEGIN
	SELECT A FROM tab WHERE 1=2
	IF @@ROWCOUNT = 0
	BEGIN 
		PRINT 'no data found'
	END
	ELSE
	BEGIN
		PRINT 'PERFORM OTHER ACTION'
	END
END

Or try EXISTS or NOT EXISTS:

BEGIN
	IF NOT EXISTS (SELECT A FROM tab WHERE 1=2)
	BEGIN
		PRINT 'no data found'
	END
	ELSE	
	BEGIN 
		PRINT 'PERFORM OTHER ACTION'
	END
END


BEGIN
	IF EXISTS (SELECT A FROM tab WHERE 1=2)
	BEGIN
		PRINT 'PERFORM OTHER ACTION'
	END
	ELSE	
	BEGIN 
		PRINT 'no data found'
	END
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-02-19 : 16:26:40

Just for further clarification - exec this code. the result is 1 even though the subquery returns no rows. That is because you are assigning a value to a variable (even if it's null)


declare @a numeric
select @a = (select id from sysobjects where 1=2)
select @@rowcount

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-19 : 18:22:26

IF NOT EXISTS (SELECT * FROM Table)
PRINT 'NO DATA FOUND'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 00:02:49

quote:
Originally posted by aakcse

why in both the occassion it is printing the same out put, could you plz tell me how to check

SELECT A FROM tab WHERE 1=1 then I want to print
'PERFORM OTHER ACTION'
SELECT A FROM tab WHERE 1=2 then I want to print
'no data found'

Actually I am trying to implement when_no_data found exception of oracle in to sqlserver, for me when no records is selected then no_data_found else preform other action..

CREATE TABLE tab ( A SMALLINT)
INSERT INTO tab VALUES(1)

DECLARE
@A NUMERIC
BEGIN
SELECT @A = (SELECT A FROM tab WHERE 1=1)
IF @@ROWCOUNT = 0
BEGIN
PRINT 'no data found'
END
PRINT 'PERFORM OTHER ACTION'
END

output--> PERFORM OTHER ACTION

DECLARE
@A NUMERIC
BEGIN
SELECT @A = (SELECT A FROM tab WHERE 1=2)
IF @@ROWCOUNT = 0
BEGIN
PRINT 'no data found'
END
PRINT 'PERFORM OTHER ACTION'
END

output--> PERFORM OTHER ACTION

Make sure you specify a scale as well as precision when you use numeric fields or variable or else this is what may happen

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-20 : 04:33:25

Thanks all, I would be very thankful if you can help me in writing below code in sqlserver

I was converting oracle code to sqlserver one, I found the situations where in Oracle we have, can you please help me in implementing the below code in sqlserver, as I was more dependent on @@rowcount but it failed as above, coz I was using @var= select



--situation one

begin 

    select a,b into v1,v2 from tab where ...
    select x,y into v3,v4 from tab2 where..
exception    -- as per my understanding this exception is raised when any of the above select fails to retrive data ( i mean no rec)
    when_no_data found then
    v1= some value
    v2= some value
end

--situation two

begin 

    select a,b into v1,v2 from tab where ...
    select x,y into v3,v4 from tab2 where..
exception   -- as per my understanding this exception is raised when any thing fails in between begin and exeption
    when others then
    v1= some value
    v2= some value
end

I feel there should have been only one select in one block, unfortunately I have got this code to convert... I felt happy to see @@rowcount will work, but it failed for select @variable = , as above.

kindly help..

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 04:38:03

For "no rows found" I think you will have to do:

begin 
DECLARE @MyRowCountFlag int

    select a,b into v1,v2 from tab where ...
    IF @@ROWCOUNT=0 SELECT @MyRowCountFlag=1

    select x,y into v3,v4 from tab2 where..
    IF @@ROWCOUNT=0 SELECT @MyRowCountFlag=2
exception    -- as per my understanding this exception is raised when any of the above select fails to retrive data ( i mean no rec)
    IF @MyRowCountFlag IS NOT NULL
    BEGIN
    when_no_data found then
    v1= some value
    v2= some value
    END

"situation two" " when any thing fails in between begin and exeption" for this you need to use TRY ... CATCH. There are things in SQL Server that this will NOT catch (invalid table / column name where late-binding is used, for example)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-20 : 04:53:14

Thanks Kirsten, I'll try to implement this and get back to you, however I am not able to connect now, I'll try today evening late, and update you.

I am very thankful to you and sqlteam.

Best regards,
aak

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-21 : 07:17:14

Hi Kirsten,

Select a,b into v1,v2 from tab.. is giving syntax error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 08:45:57

quote:
Originally posted by aakcse

Hi Kirsten,

Select a,b into v1~~,v2~~ from tab.. is giving syntax error

you cant insert result to two tables. you can specify only a single table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-21 : 08:49:03

I have no idea what that does in Oracle. TO insert into an existing table the SQL Server syntax is:


INSERT INTO MyTable(Col1, Col2, ...)
SELECT Value1, Value2, ...
FROM SomeTable

See Microsoft migration notes:
http://technet.microsoft.com/en-gb/library/bb497070.aspx

Microsoft also have a Migration Advisor - that may convert your code for you, or make suggestions. (I have no experience of using it)
http://www.microsoft.com/downloads/details.aspx?FamilyId=3E8722AE-77F3-4288-906D-F421E24D322F&displaylang=en

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 08:53:06

quote:
Originally posted by Kristen

I have no idea what that does in Oracle. TO insert into an existing table the SQL Server syntax is:

INSERT INTO MyTable(Col1, Col2, ...)
SELECT Value1, Value2, ...
FROM SomeTable

See Microsoft migration notes:
http://technet.microsoft.com/en-gb/library/bb497070.aspx

Microsoft also have a Migration Advisor - that may convert your code for you, or make suggestions. (I have no experience of using it)
http://www.microsoft.com/downloads/details.aspx?FamilyId=3E8722AE-77F3-4288-906D-F421E24D322F&displaylang=en

Aha so OP was using Oracle. I missed that part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 00:00:40

quote:
Originally posted by aakcse

Hi Kirsten,

Select a,b into v1,v2 from tab.. is giving syntax error

Have tow variables and use

declare @v1 varchar(100) -- change it if needed
declare @v2 varchar(100) -- change it if needed

select @v1=v1,@v2=v2 from table

But you cant have array datatype here
You may need to use different method

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 00:03:09

<<
I have no idea what that does in Oracle.
>>

Take column values and assign to variables (Like array)

Madhivanan

Failing to plan is Planning to fail

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-23 : 12:20:30

Thanks Visakh, Kirsten and Maddy... the below worked for me

CREATE TABLE tab ( A SMALLINT)
INSERT INTO tab VALUES(1)

DECLARE
@A NUMERIC
BEGIN
SELECT @A = A FROM tab WHERE 1=1
IF @@ROWCOUNT = 1
BEGIN
PRINT 'no data found'
END
PRINT 'PERFORM OTHER ACTION'
END
'no data found'
output--> PERFORM OTHER ACTION

DECLARE
@A NUMERIC
BEGIN
SELECT @A = A FROM tab WHERE 1=2
IF @@ROWCOUNT = 0
BEGIN
PRINT 'no data found'
END
PRINT 'PERFORM OTHER ACTION'
END

no data found
PERFORM OTHER ACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 12:23:29

always sepecify a precision and scale value for numeric variables or see what will happen below

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources