存储过程

USE [YuLinEducation_DB]
GO

/****** Object:  StoredProcedure [dbo].[usp_MakerLookTeacherInfoPage]    Script Date: 07/25/2010 12:19:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <蔺宜忠>
-- Create date: <2010-7-23>
-- Description: <通过课程类型查询该课程授课老师基本信息>
-- =============================================
ALTER PROCEDURE [dbo].[usp_MakerLookTeacherInfoPage] --存储过程的名称
 -- Add the parameters for the stored procedure here
    @pTranType     varchar(10),       --查询结果类型,总条数,或结果
 @PageIndex  int = 0,           --显示第几页
 @PageSize  int = 0,           --每页条数
 @OrderBy  nvarchar(100) = '',--排序字段
--下面为根据情况而定义的变量
    @CourseTypeID   varchar(20)        --课程类型的编号
AS
DECLARE @Sql nvarchar(Max)             --最终的查询语句
DECLARE @ConditionSQL nvarchar(2000)   --查询的条件
DECLARE @DataBaseSQL nvarchar(2000)    --表之间的关系
BEGIN
 SET @Sql = ''
 SET @ConditionSQL = ' where 1<>0 '
 IF  @CourseTypeID <> ''
  SET @ConditionSQL = @ConditionSQL + ' and coursetypecode ='''+@CourseTypeID+''' '
  set @ConditionSQL =@ConditionSQL+' )'

    SET @DataBaseSQL=' '
 IF @pTranType = 'COUNT'           --查询出结果的总条数
 BEGIN
  SET @Sql = 'select count(DISTINCT teachernumber) from teacher  where teachernumber in (select teachernumber from course  '
  SET @Sql = @Sql +@DataBaseSQL+@ConditionSQL
     print @Sql
  EXEC(@Sql)
  RETURN 0
 END

 IF @pTranType = 'PAGE'            --查询出结果
 begin
  DECLARE @FirstSQL nvarchar(2000)
  DECLARE @LastSQL nvarchar(1000)
  SET @FirstSQL = 'select * from (select  *,ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS RowNum from teacher
                    where teachernumber in (select teachernumber from course'  
  SET @LastSQL = ''
  +' ) as tmpTable WHERE tmpTable.RowNum >= (' + CAST(@PageIndex AS varchar(10)) + '-1) * '
   + CAST(@PageSize AS varchar(10)) + ' + 1 and tmpTable.RowNum <= ' + CAST(@PageIndex * @PageSize AS varchar(20))
  SET @SQL = @FirstSQL + @DataBaseSQL + @ConditionSQL  + @LastSQL
  PRINT @SQL
  EXEC(@SQL)
 END
END
GO

--exec [usp_MakerLookTeacherInfoPage]  'page',1,2,'teachernumber','ct00005'