row count

• oracle_coorgi

  SSC-Addicted

  Points: 483

  More actions

  September 4, 2006 at 3:07 am

  #75051

  hi

  i need return rowcount if its >1 or case1 or case2

  DECLARE

  @rc tinyint

  SET NOCOUNT ON

  SELECT ed.tpa_id,

  ed.division_key,

  emd.tpa_id,

  emd.empe_key

  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

  order by case when @rc=1 THEN RCONE

  WHEN @rc>2 THEN RCTWO

  END TCASE

  --some thing like this

  CREATE procedure GetDivisionKeyAssigEmployee @empid varchar(20),@tpid varchar(20),@divstr varchar(20), @rs tinyint OUTPUT

  as

  declare @ekey char(2), @divs2 varchar(10)

  if exists(select empe_key from vw_employee where tpa_id=@tpid and empr_id=@empid)

  select @ekey=empe_key from vw_employee where tpa_id=@tpid and empr_id=@empid

  select @divs2=division_key from vw_employee_misc_data where tpa_id=@tpid and empe_key=@ekey

  if @divs2=substring(@divstr,1,charindex(',',@divstr,1)-1)

  select @rs=1

  else

  if @divs2=substring(@divstr,charindex(',',@divstr,1)+1,2)

  select @rs=1

  else

  if @divs2=substring(@divstr,charindex(',',@divstr,charindex(',',@divstr,1)+1),2)

  select @rs=1

  else

  select @rs=0

  GO

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736642

  More actions

  September 4, 2006 at 9:23 am

  #658375

  This doens't make sense? You aren't setting a value for the variable in the firts cawe and not sure where you want rowcount to appear from the 2nd.

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 4, 2006 at 6:19 pm

  #658444

  Can you please explain the problem with giving us the code?

   

  Are you trying to display RCONE if row count is 1 and RCTWO if its greater than 2 (How about if its 2?) Please clarify.

  Thanks

  Sreejith