-----------------------
--登陆验证
CREATE PROC proc_userInfo
@userName char(10)='0',
@pwd char(10)='0',
@Auth char(20) = '0'
AS
select * from usersInfo WHERE UserName = @userName and UserPwd = @pwd and Authority =@Auth
GO
-------------------------------------
CREATE PROC proc_userInfo2
AS
SELECT * FROM usersInfo
GO
--------------------------------------------
Create PROC procUserInfoInsert
@UserName char(20) = '0',
@UsrPwd char(10)= '0',
@Authority char(20)= '0'
AS
INSERT INTO usersinfo(UserName,UserPwd,Authority) VALUES(@UserName,@UsrPwd,@Authority)
GO
-------------------------------------------------------
CREATE PROC procUpdataUserInfo
@UserName char(20),
@UserPwd char(10),
@Authority char(20),
@UserID INT
AS
UPDATE usersInfo SET UserName = @UserName ,UserPwd = @UserPwd ,Authority = @Authority WHERE UserID = @UserID
GO
----------------------------------------------------------------
CREATE PROC procDeleteUserInfo
@UserID int
AS
DELETE usersInfo WHERE UserID = @UserID
GO
----------------------------------------------
-----1、在使用真分页时需要使用T-SQL创建一个分页的存储过程，具体代码如下：
CREATE PROCEDURE proc_Page
@Table VARCHAR(1000), --表名
@Primarykey VARCHAR(100), --主键
@Condition VARCHAR(5000), --查询条件
@PageNumber INT, --开始页数
@PageSize INT, --每页大小
@IsCount BIT --是否获得记录数，0为否
AS
DECLARE @SQL VARCHAR(8000)
IF @IsCount != 0
SET @SQL = 'SELECT Count(*) FROM ' + @Table + ' WHERE ' + @Condition
ELSE
BEGIN
IF @PageNumber = 1
SET @SQL = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ' + @Table + ' WHERE ' + @Condition
ELSE
SET @SQL = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ' + @Table +
' WHERE ' + @Primarykey + ' NOT IN (SELECT TOP ' + STR(@PageSize*(@PageNumber - 1))
+ ' ' + @Primarykey + ' FROM ' + @Table + ' WHERE ' + @Condition + ') AND ' + @Condition
END
EXEC(@SQL)
GO
-----------------------------------------------------------------------------------------------
CREATE PROC procUpdatePwdUserInfo
@UserPwd CHAR(10),
@UserName CHAR(20)
AS
UPDATE UsersInfo set UserPwd = @UserPwd WHERE UserName = @UserName
GO
------------------------------------------------
CREATE PROC procUpdatePwdUserInfo2
@userName CHAR(20),
@pwd CHAR(10)
AS
SELECT * FROM usersInfo WHERE UserName = @UserName AND UserPwd = @pwd
GO
-----------------------------------------------------------
CREATE PROC procInsertCardsInfo
@cardID char(10),
@ReaderName char(20),
@ReaderSex char(2),
@readerBirth char(20),
@readerTel char(13),
@ReaderAdd char(50),
@CArdStatus char(50) ,
@charTypeID int,
@BoorowedCount int
AS
INSERT INTO cardsInfo VALUES(@cardID,@ReaderName,@ReaderSex,@readerBirth,@readerTel,@ReaderAdd,@CArdStatus,@charTypeID,@BoorowedCount)
GO
--------------------------------------------------------------
--对CardsInfo修改
CREATE PROC procUpdateCardsInfo
@cardID char(10),
@ReaderName char(20),
@ReaderSex char(2),
@readerBirth char(20),
@readerTel char(13),
@ReaderAdd char(50),
@CArdStatus char(50) ,
@charTypeID int,
@BoorowedCount int
AS
UPDATE cardsInfo SET ReaderName = @ReaderName,ReaderSex = @ReaderSex,ReaderBirth = @readerBirth,
ReaderTel = @readerTel,ReaderAddr = @ReaderAdd,CardStatus = @CArdStatus, CardTypeId= @charTypeID,BorrowedCount = @BoorowedCount
WHERE CardID = @cardID
GO
--------------------------------------------------------------
CREATE PROC procSelectCardStyle
AS
SELECT * FROM cardStyle
GO
----------------------------------------
CREATE PROC procSelectStatuNumber
@statu VARCHAR(10)
AS
SELECT * FROM cardStyle WHERE CardStyleName = @statu
GO
------------------------------------------------------
CREATE PROC procSelectCardStyleName
@id int
AS
SELECT * FROM cardStyle WHERE CardStyleID = @id
GO
----------------------------------------------------------
CREATE PROC procSelectAsUserName
@UserName CHAR(10)
AS
SELECT * FROM UsersInfo WHERE UserName = @UserName
GO
------------------------------------------------
--图书信息插入
create proc procInsertBooksInfo ---强正斌
@BookID char(10),
@BookName char(20),
@WritterName char(10),
@PublisherID char(4),
@PublishDate datetime,
@TypeID char(4),
@LentCount int,
@Status char(10),
@InDate datetime,
@BookView char(1000),
@BookImage char(100)
as
INSERT INTO booksInfo VALUES(@BookID,@BookName,@WritterName,@PublisherID,@PublishDate,@TypeID,@LentCount,@Status,@InDate,@BookView,@BookImage)
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------
--按图书编号查询图书信息
CREATE PROC procSelectBookInfoAsBookID
@BookID CHAR(10)
AS
SELECT * FROM booksInfo WHERE BookID = @BookID
GO
-------------------------------------------------
--按图书名查询图书信息
CREATE PROC procSelectBookInfoAsBookName
@BookName CHAR(20)
AS
SELECT * FROM booksInfo WHERE BookName = @BookName
GO
-------------------------------------------------
--按作者查询图书信息
CREATE PROC procSelectBookInfoAsAuth
@Auth CHAR(20)
AS
SELECT * FROM booksInfo WHERE WritterName = @Auth
GO
-----------------------------------------------------------
----按图书名查询图书信息
CREATE PROC procSelectBookInfoAsType
@Style CHAR(20)
AS
SELECT * FROM booksInfo WHERE TypeID = @Style
GO
--select * from publisherInfo
-----------------------------------------------------
CREATE PROC procSelectPublisherIDAsPulisher
@Publisher CHAR(20)
AS
SELECT * FROM publisherInfo WHERE PublisherName = @Publisher
GO
-------------------------------------------------
CREATE PROC procBookInfoAsPublisherID
@ID char(4)
AS
SELECT * FROM booksInfo WHERE PublisherID = @ID
GO
---------------------------------------------------------
create PROC SelectBookStyleASID
@Type CHAR(10)
AS
SELECT * FROM bookStyle WHERE BookStyle = @Type
GO
-----------------------------------------
--按图书编号删除图书信息
CREATE PROC procDeleteBookInfoAsBookID
@BookID CHAR(10)
AS
DELETE booksInfo WHERE BookID = @BookID
GO
----------------------------------------------------------
--显示卡的所有信息
CREATE PROC procSelectAllCardsInfo
AS
select * from cardsInfo
GO
-----------------------------------------------------
--按卡号查询卡的信息
CREATE PROC procSelectAsCardsID
@cardID CHAR(10)
AS
SELECT * FROM cardsInfo WHERE cardID = @cardID
GO
----------------------------------------------------
--按y用户名查询卡的信息
CREATE PROC procSelectAsReaderName
@name CHAR(10)
AS
SELECT * FROM cardsInfo WHERE ReaderName = @name
GO
--------------------------------------------------------
---按图书编号修改图书信息
CREATE PROC procUpdateBooksInfo
@BookID char(10),
@BookName char(20),
@WritterName char(10),
@PublisherID char(4),
@PublishDate datetime,
@TypeID char(4),
@LentCount int,
@Status char(10),
@InDate datetime,
@BookView char(1000),
@BookImage char(100)
AS
UPDATE BooksInfo SET BookName = @BookName,WritterName = @WritterName,PublisherID = @PublisherID,
PublishDate = @PublishDate,TypeID = @TypeID,LentCount = @LentCount,Status = @Status,
InDate = @InDate,BookView = @BookView,BookImage = @BookImage WHERE BookID = @BookID
GO
---------------------------------------------------------------------------------------
--按卡号删除卡的信息
CREATE PROC procDeleteCardsInfoAsCardID
@cardID CHAR(10)
AS
DELETE cardsInfo WHERE CardID = @cardID
GO
-----------------------------------------------------
--添加出版社
CREATE PROC procInsertIntoPublisher
@ID CHAR(4),
@PName CHAR(20)
AS
INSERT INTO publisherInfo VALUES(@ID,@PName)
GO
---------------------------------------------------------------
--添加图书类别
CREATE PROC procInsertIntoBookStyle
@ID CHAR(4),
@Style CHAR(10)
AS
评论1
最新资源