case function with row count

Question

case function with row count

oracle_coorgi

SSC-Addicted

Points: 483
More actions
September 4, 2006 at 10:11 pm

#114313

hi
pls give me eg for case function with row count
eg: if the row count is >1 then HIGH <1 or zero low
---these r table and column for refernce--
DECLARE
@rc tinyint ,
@var varchar(50)
set @var = (SELECT emd.tpa_id
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD
where emd.tpa_id=ed.tpa_id
and
emd.empe_key=ed.division_key)
thanxs
india-bangalore

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

The function @@ROWCOUNT is updated only and after every DML statement is execueted.. This function can be used in this way....

DECLARE @rc tinyint , @var varchar(50)

SELECT @var = emd.tpa_id from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD

where emd.tpa_id=ed.tpa_id and emd.empe_key=ed.division_key

SET @rc = @@ROWCOUNT

PRINT @rc

Note, you've used a select statement which will result a subquery error if the table contains more than one record..

--Ramesh

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Also see this thread or topic

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71510

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I can't tell if you want "High/Low" returned as a literal or a boolean... this will do the boolean thing... let us know what you really need.

DECLARE @MyCase INT

... do something like a select, update, insert, delete...

SELECT @MyCase = SIGN(@@ROWCOUNT)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)