新建 文本文档.txt

数据库文件先要导入

-----------------------------------usp------------------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
   ---删除admini用户
if exists (select name from sysobjects where name='usp_deleteadmini' and type= 'p')
   drop proc usp_deleteadmini
go
create proc usp_deleteadmini
  @admininame varchar(20) 
as 
  if exists (select loginname from admini  where loginname=@admininame)
begin
   delete from admini
            where loginname=@admininame
end
else
     raiserror('不存在此用户',16,1) 




go                
-------------------------------------------------------------------------------------------------------
 ------------添加admini用户------------------------------------------------------------------------
----------------------------------------------------------------------------------------

if exists (select name from sysobjects where name='usp_addadmini' and type= 'p')
   drop proc usp_addadmini
go
create proc usp_addadmini 
  @loginname varchar(20), 
   @password varchar(20)
      
as 
     if not exists(select loginname from admini  where loginname=@loginname) 
    begin 
     insert into admini
     values
    (@loginname,@password)
   end
   else
       raiserror('已经存在此用户,请更改用户名',16,1)
 
go
---------------------------------------------------------------------------------------------
---------changepassword------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

if exists (select name from sysobjects where name='usp_changePassword' and type ='p ')
   drop proc usp_changePassword
go

create  proc usp_changePassword
  @LoginName varchar(20),
    @oldpassword varchar(20),
      @newpassword varchar(20)
     
as 
          update admini
              set [password]=@newpassword
           WHERE (loginname=@LoginName and [password]=@oldpassword)

go

----------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
--------------------- admini验证------------------------------------------------
if exists (select name from sysobjects where name='usp_checkUser' and type ='p ')
   drop proc usp_checkUser
go

create  proc usp_checkUser
  @LoginName varchar(20),
    @password varchar(20)
as 
    select count(*) from admini 
           where (loginname=@LoginName and [password]=@password)
     
go
-------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------

--------添加书----------------

   
if exists (select name from sysobjects where name='usp_addnewbook' and type ='p ')
   drop proc usp_addnewbook

go
create proc usp_addnewbook

        @ISBN char(13),
          @type char(20),
           @name char(20),
             @author char(20),
               @publish nvarchar(40),
	         @number int,
	            @price money,
	                @memo nvarchar(255)
as 
  if not exists(select isbn from books where isbn=@isbn)
      begin
            insert into books
                    values
                     (@ISBN,@type,@name,@author,@publish,@number,@price,@memo)
      end
  else
      raiserror('已经存在书',16,1)

go

-------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

------------------删除---------------
if exists (select name from sysobjects where name='usp_deletebook' and type ='p ')
   drop proc usp_deletebook

go
create proc usp_deletebook
          @ISBN char(13)
as 
    if exists(select isbn from books where isbn=@isbn)
      begin
            delete from books
                   where isbn=@isbn 
      end
  else
      raiserror('不存在此书',16,1)


go
---------------------------------------
------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

---------------------------更新---------------------------------------
if exists (select name from sysobjects where name='usp_updatebook' and type ='p ')
   drop proc usp_updatebook

go
create proc usp_updatebook
        @ISBN char(13),
          @type char(20),
           @name char(20),
             @author char(20),
               @publish nvarchar(40),
	         @number int,
	            @price money,
	                @memo nvarchar(255)
as 
  if exists(select isbn from books where isbn=@isbn)
      begin
            update books
              set
                type=@type,
                 [name]=@name,
		   author=@author,
		     publish=@publish,
		       number=@number,
		         price=@price,
		          memo=@memo
            where isbn=@ISBN
      end
  else
      raiserror('不存在此书',16,1)

go
--------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
--------------------------------删除读者--------------------------------------------
if exists (select name from sysobjects where name='usp_deletereader' and type= 'p')
   drop proc usp_deletereader
go
create proc usp_deletereader
  @name varchar(20) 
as 
  if exists (select name from reader  where name=@name)
begin
   delete from reader
            where name=@name
end
else
     raiserror('不存在此用户',16,1) 

go                
-------------------------------------------------------------------------------------------------------
 ------------添加reader------------------------------------------------------------------------
----------------------------------------------------------------------------------------

if exists (select name from sysobjects where name='usp_addreader' and type= 'p')
   drop proc usp_addreader
go
create proc usp_addreader
        @name char(20),
	@sex char(4),
	@password char(20),
	@number int,
	@e_mail  char(50),
	@memo nvarchar(255)
as 
      insert into reader
           values
      (@name,@sex,@password,@number,@e_mail,@memo)
go

---------------------------------------------------------------------------------------------
---------changepassword------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

if exists (select name from sysobjects where name='usp_changeReaderPassword' and type ='p ')
   drop proc usp_changeReaderPassword
go

create  proc usp_changeReaderPassword
  @Name varchar(20),
    @oldpassword varchar(20),
      @newpassword varchar(20)
     
as 
          update reader
              set [password]=@newpassword
           WHERE ([name]=@Name and [password]=@oldpassword)

go

----------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
--------------------- admini验证------------------------------------------------
if exists (select name from sysobjects where name='usp_checkReader' and type ='p ')
   drop proc usp_checkReader
go

create  proc usp_checkReader
  @Name varchar(20),
    @password varchar(20)
as 
    select count(*) from reader 
           where ([name]=@Name and [password]=@password)
     
go