ONE PERTICULARE COLUMN,HOW TO GET NEXT VALUE

ONE PERTICULARE COLUMN,HOW TO GET NEXT VALUE

 

 

select ecode ,ROW_NUMBER() OVER (ORDER BY ecode) AS 'Row Number'

from (select distinct ecode

 from tblmanual_process )a

ecode	Row Number
C00001	1
C00002	2
C00003	3
C00004	4
C00005	5

 

--Create this Function

Create FUNCTION [dbo].[Fcalc]   

(     

@ecode varchar(50)    

)     

RETURNS  varchar(50)      

AS     

BEGIN     

declare @Result varchar(50)

declare @er varchar(50),@no int,@mn int

select @er=ecode,@no=[Row Number]   from(

select ecode ,ROW_NUMBER() OVER (ORDER BY ecode) AS [Row Number]

from (select distinct ecode

 from tblmanual_process )a ) b where b.ecode=@ecode

--

 select @Result=ecode,@mn=[Row Number]   from(

select ecode ,ROW_NUMBER() OVER (ORDER BY ecode) AS [Row Number]

from (select distinct ecode

 from tblmanual_process )a  ) b where  b.[Row Number]=@no+1

  

 --    

RETURN ISNULL(@Result,'')     

END

--Exec this one

-- select [dbo].[Fcalc]('C00001')

Result

Ecode

C00002