How do i use rowcount in Function in sqlserver?

Question

I have faced a problem using rowcount in function in sql server 2000. It shows an error like Invalid use of 'UNKNOWN TOKEN' within a function.

MY function is like this.

ALTER Function fnc_GetOpenShiftWorkID (@EMP_ID int,@Counter int,@date Datetime) returns int as
BEGIN

  SET ROWCOUNT @Counter
  declare @result int

  if exists(select * from tbl_org_workinghrs WHERE EMP_ID=@EMP_ID and SDATE=@DATE)  
  BEGIN

    select  @result= WORK_ID 
    from    tbl_org_working_hrs work_hrs
            inner join tbl_org_shift_group sgroup on sgroup.WH_ID=work_hrs.WORK_ID
            inner join tbl_org_workinghrs workhrs on workhrs.GROUP_ID=sgroup.GROUP_ID
    WHERE   EMP_ID=@EMP_ID 
            and SDATE=@DATE 
    order by 
            IN_START
  END
  ELSE
  BEGIN
    if exists(select * from tbl_org_workinghrs where EMP_ID=0)
    BEGIN
      select  @result=WORK_ID 
      from    tbl_org_working_hrs 
      WHERE   IS_DEFAULTSHIFT=1
    END
  END

  return @result
END

Not related to your question but you should decide on using upper- or lowercase for reserved words. Either one is nice to read but mixing them gives a sloppy appearance. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Oct 17, 2011 at 6:15
Why do you want to do this in the function SET ROWCOUNT @Counter? What effect do you think/want it will have? — Mikael Eriksson
– Mikael Eriksson, Commented Oct 17, 2011 at 6:16
@MikaelEriksson:Actually i need the last rows value in which row no. is not fixed. — Prakash Kunwar
– Prakash Kunwar, Commented Oct 17, 2011 at 6:20
In @result you are selecting the EMP_ID, then what is the use of @COunt? — Prasanth
– Prasanth, Commented Oct 17, 2011 at 6:33

Mikael Eriksson · Accepted Answer · 2011-10-17 06:40:52Z

1

You want to get the value of the n'th row ordered by IN_START.

From SQL Server 2005 later you could use top(n) or row_number().

In SQL Server 2000 you can use a table variable with an identity ID field as a temp storage.

Something like this.

declare @T table
(
  ID int identity, 
  WORK_ID int
)

insert into @T (WORK_ID)
select WORK_ID
from tbl_org_working_hrs work_hrs
  inner join tbl_org_shift_group sgroup 
    on sgroup.WH_ID=work_hrs.WORK_ID
  inner join tbl_org_workinghrs workhrs 
    on workhrs.GROUP_ID=sgroup.GROUP_ID
where EMP_ID=@EMP_ID and 
      SDATE=@DATE 
order by IN_START

select @result = WORK_ID
from @T
where ID = @Counter

answered Oct 17, 2011 at 6:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do i use rowcount in Function in sqlserver?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related