SQL user-defined scalar value function that looks up relevant column values n times forward or backward according to a specified table name, column name and column value

Sometimes you need to move forward or backward according to the value of a specified column N Check the relevant column values , Then use this SQL Custom scalar valued functions can get the relevant SQL sentence ：

ALTER FUNCTION GetPreviousNextCodeSql
(
 @tableName varchar(50),
 @codeFieldName varchar(50),
 @currentCode varchar(50),
 @idKey varchar(50),
 @step int
) --('B_ProcessList','process_code','OCV3','processid',-1)
RETURNS varchar(2000)
AS
BEGIN
 DECLARE @result varchar(50), @sql varchar(2000)
 set @result=''
-- To determine if there is idx Name , if there be , Use idx Sort , Otherwise use processid Sort
 --if exists(select 1 from syscolumns where name='idx' and id=object_id(@tableName))
  --Begin
   set @sql = 'select t.'+ @codeFieldName +' from ('
   set @sql = @sql +'select row_number() over (order by ' + @idKey +' asc) as p, ' + @codeFieldName +' from ' + @tableName
   set @sql = @sql +') as t'
   set @sql = @sql + ' where t.p =('
   set @sql = @sql +' select top 1 m.p'
   IF @step>0
                      set @sql = @sql +'+'
                  ELSE
                   set @sql = @sql
   set @sql = @sql + convert(varchar,@step)
   set @sql = @sql +' from ('
   set @sql = @sql +' select row_number() over (order by ' + @idKey +' asc) as p, ' + @codeFieldName +' from  ' + @tableName
   set @sql = @sql +') as m where ' + @codeFieldName + '=''' + @currentCode +''')'
   return @sql
  --End
--return ''
END

Invocation time （ give an example ）：

-- Check the second record above
select dbo.GetPreviousNextCodeSql('B_ProcessList','process_code','OCV3','processid',-2)
-- Check the second record
select dbo.GetPreviousNextCodeSql('B_Process_Flow','flow_code','GY002','id',+1)

The statement generated after the call is similar to the following ：

select t.process_code from (select row_number() over (order by processid asc) as p, process_code from B_ProcessList) as t where t.p =( select m.p-2 from ( select row_number() over (order by processid asc) as p, process_code from  B_ProcessList) as m where process_code='OCV3')

Key statement ：

-- Check the previous process record
select t.process_code from (
select row_number() over (order by processid asc) as p, process_code from B_ProcessList
) as t
where t.p =(
select m.p-1 from (
select row_number() over (order by processid asc) as p, process_code from B_ProcessList
) as m where process_code='OCV3')

Of course, you can also use and similar statements to realize , But the flexibility is not as good as the above , Because you can only look up or down / The latter ：

-- Check the next process record
select process_code from B_ProcessList  where processid=
(select max(processid) from B_ProcessList where processid<
(select processid from B_ProcessList where process_code='OCV3'))
-- Check the previous process record
select process_code from B_ProcessList  where processid=
(select min(processid) from B_ProcessList where processid>
(select processid from B_ProcessList where process_code='OCV3'))

OK.