CREATE PROC sp_ListDepartment @DepNo nvarchar(10), @ListType int, @strWhere nvarchar(1000)='' /* ListType=0 非上下筆 ListType=1 首筆 ListType=2 上一筆 ListType=3 后一筆 ListType=4 尾筆 */ AS SET NOCOUNT ON IF @strWhere='' SET @strWhere=' WHERE 1=1 ' ELSE SET @strWhere=' WHERE ' +@strWhere DECLARE @strSQL nvarchar(1000) IF @DepNo<>'' and @ListType=0 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo=''' + @DepNo + '''' ELSE IF @DepNo='' and @ListType=0 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo DESC' ELSE IF @ListType=1 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo' ELSE IF @DepNo<>'' and @ListType=2 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo <''' +@DepNo + ''' ORDER BY DepNo DESC' ELSE IF @DepNo<>'' and @ListType=3 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo>''' + @DepNo + ''' ORDER BY DepNo' ELSE IF @ListType=4 SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo DESC' ELSE IF @ListType=5 SET @strSQL = 'SELECT count(*) FROM comDepartment ' + @strWhere EXEC(@strSQL) RETURN @@rowcount SET NOCOUNT OFF GO
@DepNo nvarchar(10),
@ListType int,
@strWhere nvarchar(1000)=''
/*
ListType=0 非上下筆
ListType=1 首筆
ListType=2 上一筆
ListType=3 后一筆
ListType=4 尾筆
*/
AS
SET NOCOUNT ON
IF @strWhere=''
SET @strWhere=' WHERE 1=1 '
ELSE
SET @strWhere=' WHERE ' +@strWhere
DECLARE @strSQL nvarchar(1000)
IF @DepNo<>'' and @ListType=0
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo=''' + @DepNo + ''''
ELSE IF @DepNo='' and @ListType=0
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo DESC'
ELSE IF @ListType=1
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo'
ELSE IF @DepNo<>'' and @ListType=2
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo <''' +@DepNo + ''' ORDER BY DepNo DESC'
ELSE IF @DepNo<>'' and @ListType=3
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' AND DepNo>''' + @DepNo + ''' ORDER BY DepNo'
ELSE IF @ListType=4
SET @strSQL='SELECT top 1 * FROM comDepartment ' + @strWhere + ' ORDER BY DepNo DESC'
ELSE IF @ListType=5
SET @strSQL = 'SELECT count(*) FROM comDepartment ' + @strWhere
EXEC(@strSQL)
RETURN @@rowcount
SET NOCOUNT OFF
GO