sqlserver存储过程详细代码

存储过程

exec sp_databases

declare @Studnet int

set @Studnet=243

exec sp_helptext @Studnet

exec sp_helptext 存储过程名

 

--创建

create proc usp_GetStudentById

@Id int

as

select * from Student where id=@Id

 

alter proc usp_GetStudentByIdAndName

@Id int,

@Name varchar(10)   --长度

as

select * from Student where id=@Id and name=@Name

 

alter proc usp_GetStudent       --修改 alter

as

begin

select * from Student where id='1'

select * from Student

end

 

--分页

alter proc usp_GetPageStudnetRowNum

 

@pageindex int=1

as

begin

   select * from

(

   select *,ROW_NUMBER() over(order by ID) AS rownum

   from Student) as a

   where a.rownum >(@pageindex-1)*5 and a.rownum<= @pageindex*5

end

 

exec usp_GetPageStudnetRowNum 2

 

--调用

exec sp_helptext usp_GetStudentById

 

exec usp_GetStudentById '1'   --参数

 

exec usp_GetStudentByIdAndName @Id='5',@Name='few'    --多个参数

 

exec usp_GetStudent

 

 

--分页

create proc usp_GetPageStudnetRowNum2

 

@pageindex int=1,

@countnum int=5

as

begin

   select * from

(

   select *,ROW_NUMBER() over(order by ID) AS rownum

   from Student) as a

   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum

end

 

exec usp_GetPageStudnetRowNum2    --全默认

exec usp_GetPageStudnetRowNum2 @countnum=3

exec usp_GetPageStudnetRowNum2 @pageindex=3,@countnum=3

 

--输入参数

declare @p int,@c int

set @p=2

set @c=3

exec usp_GetPageStudnetRowNum2 @p,@c

 

alter proc usp_GetPageStudnetRowNum3

 

@pageindex int=1,

@countnum int=5,

@rowcount int output

as

begin

   select * from

(

   select *,ROW_NUMBER() over(order by ID) AS rownum

   from Student) as a

   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum

   select @rowcount=COUNT(*) from Student

   set @pageindex=234

end

 

--输出

declare @p int,@c int,@co int

set @p=2

set @c=3

set @co=0

exec usp_GetPageStudnetRowNum3 @p,@c,@co output   --加 output

 

select @p,@c,@co

 

--返回总页数

 

alter proc usp_GetPageStudnetRowNum4

 

@pageindex int=1,

@countnum int=5,

@rowcount int output,

@pagecount int output

as

begin

   select * from

(

   select *,ROW_NUMBER() over(order by ID) AS rownum

   from Student) as a

   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum

   select @rowcount=COUNT(*) from Student

--   set @pagecount=@rowcount/@countnum

 set @pagecount=CEILING(CONVERT(float,@rowcount)/CONVERT(float,@countnum))

end

 

declare @p int,@c int,@co int,@coun int

set @p=2

set @c=3

set @co=0

exec usp_GetPageStudnetRowNum4 @pageindex=@p,@countnum=@c,@rowcount=@co output,@pagecount=@coun output

 

select @p,@c,@co,@coun as i

 

 

--销毁

drop proc usp_GetStudentById

 

 

--

select * from

(

select *,ROW_NUMBER() over(order by ID) AS rownum

from Student

) as a

where a.rownum<=5

 

delete from Student

drop table Studentdrop proc usp_GetStudentById为数据插入学生表创建存储过程

 

 

alter  proc INSERE_TO_STUDENT --往student中插入数据

@STUDENT_NAME NVARCHAR(20),

@STUDENT_PWD NVARCHAR(40),

@STUDENT_SEX NVARCHAR(10),

@STUDENT_AGE INT,

@NO int=1

 

AS

declare @b int

select @b=S_NO FROM S_NO WHERE S_NAME='S_NO'--取出S_NO中S_NO的值

 

set @b=@b+@NO--让其自加

 

UPDATE S_NO --将S_NO值修改后在重新放入表中

SET S_NO=@b

WHERE S_NAME='S_NO'

declare @STUDENT_NO NVARCHAR(10)

set @STUDENT_NO= CONVERT(varchar(100), Year(Getdate()))+Convert(NVARCHAR(10),@b)--CONVERT(varchar(100), GETDATE(), 112)为获取当前系统时间

 

INSERT INTO T_STUDENT(STUDENT_NO,STUDENT_PWD,STUDENT_NAME,STUDENT_SEX,STUDENT_AGE)

VALUES (@STUDENT_NO,@STUDENT_PWD,@STUDENT_NAME,@STUDENT_SEX,@STUDENT_AGE)

 

为数据插入教师表创建存储过程

 

alter proc INSERE_TO_TEACHER --往T_TEACHER中插入数据

    @NAME NVARCHAR(20),

    @PWD NVARCHAR(40),

    @SEX NVARCHAR(10),

    @TEL NVARCHAR(20),

    @N int=1

 

AS

declare @b int

select @b=T_NO FROM dbo.T_NO WHERE T_NAME='T_NAME'--取出T_NO中T_NO的值

 

set @b=@b+@N--让其自加

 

UPDATE T_NO --将S_NO值修改后在重新放入表中

SET T_NO=@b

WHERE T_NAME='T_NAME'

declare @NO NVARCHAR(10)

set @NO= CONVERT(varchar(100), Year(Getdate()))+Convert(NVARCHAR(10),@b)--CONVERT(varchar(100), GETDATE(), 112)为获取当前系统时间

 

INSERT INTO T_TEACHER(TEACHER_NO,TEACHER_PWD,TEACHER_NAME,TEACHER_SEX,TEACHER_TEL_NO)

VALUES (@NO,@PWD,@NAME,@SEX,@TEL)

 

为插入管理员对象创建存储过程

CREATE proc INSERE_TO_AMT--往T_TEACHER中插入数据

    @NAME NVARCHAR(20),

    @PWD NVARCHAR(40),

    @N int=1

 

AS

declare @b int

select @b=A_NO FROM dbo.A_NO WHERE A_NAME='A_NAME'--取出A_NO中A_NO的值

 

set @b=@b+@N--让其自加

 

UPDATE A_NO --将A_NO值修改后在重新放入表中

SET A_NO=@b

WHERE A_NAME='A_NAME'

declare @NO NVARCHAR(10)

set @NO= CONVERT(varchar(100), Year(Getdate()))+Convert(NVARCHAR(10),@b)--CONVERT(varchar(100), GETDATE(), 112)为获取当前系统时间

 

INSERT INTO dbo.T_AMT (AMT_NO,AMT_PWD,AMT_NAME)

VALUES (@NO,@PWD,@NAME)

 

为查询学生表创建视图

CREATE VIEW SELECT_STUDENT(学号,姓名,密码,性别,年龄)

AS

SELECT STUDENT_NO,STUDENT_NAME,STUDENT_PWD,STUDENT_SEX,STUDENT_AGE FROM dbo.T_STUDENT

 

为查询管理员表创建视图

 

CREATE VIEW SELECT_AMT(管理员号,姓名,密码)

AS

SELECT AMT_NO,AMT_NAME ,AMT_PWD FROM dbo.T_AMT

为查询教师表创建视图

 

CREATE VIEW SELECT_TEACHER(教师号,姓名,密码,性别,电话号码)

AS

SELECT TEACHER_NO,TEACHER_NAME,TEACHER_PWD ,TEACHER_SEX,TEACHER_TEL_NO FROM dbo.T_TEACHER

为查询所有用户的信息建立的视图

CREATE VIEW SELECT_ALL (用户名编号,姓名,密码,权限类型)

AS

SELECT STUDENT_NO,STUDENT_NAME,STUDENT_PWD,LIMM FROM dbo.T_STUDENT

union 

SELECT TEACHER_NO,TEACHER_NAME,TEACHER_PWD,LIMM FROM dbo.T_TEACHER

union 

SELECT AMT_NO,AMT_NAME,AMT_PWD,LIMM FROM dbo.T_AMT

 

select *from SELECT_ALL