存储过程增删查改

--添加
CREATE PROCEDURE p_StudentAdd
	@StudentName VARCHAR(50),
	@Gender SMALLINT,
	@ClassesCode INT
	
AS
BEGIN
	BEGIN TRAN 
		BEGIN TRY
			INSERT INTO dbo.Student
			        ( StudentName, Gender, ClassesCode )
			VALUES  (@StudentName, -- StudentName - varchar(50)
			          @Gender, -- Gender - smallint
			          @ClassesCode  -- ClassesCode - int
			          )

			COMMIT TRAN
		END TRY


		BEGIN CATCH
			ROLLBACK TRAN
		END CATCH
	


END
GO
--删除
CREATE PROCEDURE p_StudentDel
	@ID INT 
AS 
BEGIN 
	DELETE FROM dbo.Student WHERE ID=@ID

END


GO

--修改
CREATE PROCEDURE p_StudentUpt
	@StudentName VARCHAR(50),
	@Gender SMALLINT,
	@ClassesCode INT,
	@ID INT 
AS 
BEGIN
	UPDATE dbo.Student SET StudentName=@StudentName,Gender=@Gender,ClassesCode=@ClassesCode WHERE ID=@ID

END
	
GO
    
--显示+分页+模糊查询
CREATE PROCEDURE p_GetStudentList
	@PageIndex int ,--当前页
	@Pagesize FLOAT,--每页显示条数
	@PageCount INT OUTPUT,--总页数
	@StudentName VARCHAR(50),
	@ClassesCode VARCHAR(50),
	@SchoolCode VARCHAR(50) 	

AS
BEGIN
	BEGIN TRAN  
		BEGIN TRY
        
		DECLARE @sql NVARCHAR(max)
			SET @sql='SELECT a.ID,a.StudentName,a.Gender,b.ClassesCode,b.ClassesName,c.SchoolCode,
						c.SchoolName,ROW_NUMBER() OVER(ORDER BY a.ID) rownum  FROM dbo.Student a INNER JOIN dbo.Classes b
						ON b.ClassesCode = a.ClassesCode INNER JOIN dbo.School c ON c.SchoolCode = b.SchoolCode where 1=1 '
			IF(@StudentName!= '')
				SET @sql+=' and a.StudentName='''+@StudentName+'''';
			IF (@ClassesCode!='')
				SET @sql+=' and b.ClassesCode='''+@ClassesCode+'''';
			IF(@SchoolCode!='')
				SET @sql+='and c.SchoolCode='''+@SchoolCode+'''';
		DECLARE @sql2 NVARCHAR(max)
			SET @sql2='SELECT * FROM ('+@sql+') t WHERE t.rownum BETWEEN '+STR(((@PageIndex-1)*@Pagesize+1)) +'AND'+ STR(@PageIndex*@Pagesize)
			PRINT @sql2
			EXEC (@sql2)

			DECLARE @count FLOAT
			EXEC (@sql)
				SET @count=@@ROWCOUNT
				SET @PageCount=CEILING(@count/@Pagesize)
		
			COMMIT TRAN 
		END TRY
        

		BEGIN CATCH
			ROLLBACK TRAN
		END CATCH


END
 
GO 
 DECLARE @PageCount INT 
 EXEC p_GetStudentList 1,2,@PageCount OUTPUT,'','',''
 
 GO
 

--根据ID 查询
CREATE PROCEDURE p_GetStudentListByID
	@ID INT 
AS
BEGIN
	SELECT a.ID,a.StudentName,a.Gender,b.SchoolCode,a.ClassesCode FROM dbo.Student a INNER JOIN dbo.Classes b ON b.ClassesCode = a.ClassesCode  WHERE a.ID=@ID
END
GO
EXEC dbo.p_GetStudentListByID @ID = 7 -- int
GO

--修改
CREATE PROCEDURE p_UpdateStudentList
	@StudentName VARCHAR(50),
	@Gender SMALLINT,
	@ClassesCode INT,
	@ID INT 
AS
BEGIN
	UPDATE dbo.Student SET StudentName=@StudentName,Gender=@Gender,ClassesCode=@ClassesCode WHERE ID=@ID
END

EXEC dbo.p_UpdateStudentList @StudentName = '张三三', -- varchar(50)
    @Gender = 1, -- smallint
    @ClassesCode = 1, -- int
    @ID = 2 -- int


GO





--学校显示
CREATE PROCEDURE p_GetSchoolList
AS
BEGIN
	SELECT * FROM dbo.School

END

GO
--班级显示
CREATE PROCEDURE p_GetClassesList
	@SchoolCode INT  
AS
BEGIN
	SELECT * FROM dbo.Classes WHERE SchoolCode=@SchoolCode

END

SELECT * FROM dbo.Student