根据某一指定的表名、列名及列值来向前或向后N条查相关列值的SQL自定义标量值函数

有时需要根据某一指定的列的值来向前或向后N条查相关的列值，那么用此SQL自定义标量值函数可以取得相关的SQL语句：

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=''
 --判断是否有idx列名,如有,使用idx排序，否则使用processid排序
  --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

调用时（举例）：

--查上面第二条记录

select dbo.GetPreviousNextCodeSql('B_ProcessList','process_code','OCV3','processid',-2)

--查后面第二条记录

select dbo.GetPreviousNextCodeSql('B_Process_Flow','flow_code','GY002','id',+1)

调用后生成的语句类似如下：

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')

关键语句：

--查上一条工序记录

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')

当然也可以使和类似的语句来实现，但灵活性不如上面的做法，因为只能向上或向下查前/后一条：

--查下一条工序记录

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'))

--查上一条工序记录

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。