--创建一个向特定表中插入记录的存储方式
create proc usp_T_Users_Insert
@username varchar(50),@password varchar(50),@email varchar(50)
as
begin
insert into T_Users values (@username,@password,@email)
end
--2使用ADO验证登陆。
create table T_User (FuserName nvarchar(50),Fpassword varchar(50))
insert into T_User values('admin','123')
insert into T_User values('YQQ','123')
create proc usp_School_denglu
@useName varchar(50),@password varchar(50),@result bit output
as
begin
declare @count int
set @count=(select COUNT(*) from T_User where FuserName=@useName and Fpassword=@password)
if @count>0
begin
set @result=1
end
else
begin
set @result=0
end
end
declare @r bit
exec usp_School_denglu 'admin','888888',@r output
print @r
--2.1使用ADO重写登陆验证存储过程。
create proc usp_chkLogin2
@username varchar(50),
@pwd varchar(50)
as
begin
select COUNT(*) from T_User where Fusemame=@username and Fpassword=@pwd
end
exec usp_chkLogin2 'YQQ43','123'
--删除特定表中的某条记录
create proc usp_Student_Delete
@tTId int
as
begin
delete from myteacher where tTId=@tTId
end
--修改特定表中的某条记录(更新)
create proc usp_ClassBak_update
@Cid int,
@Cname varchar(50)
as
begin
update ClassBak set cName=@Cname where clsId=@Cid
end
drop proc uso_class_selectAll
--查询所有记录的存储过程
create proc uso_class_selectAll
as
begin
select * from ClassBak
end
exec uso_class_selectAll
--3 写一个对特定表进行分页显示的存储过程,要求有两个参数一个是 每页显示的记录的条数(@pagesize),第二个是显示第几页(@pageIndex)
create proc usp_MyStudent_GetDateByPageIndex
@pagesize int=10,
@pageIndex int
as
begin
select * from
(select *,ROW_NUMBER() over(order by Fid) as rowIndex from MyStudents) as tb1
where tbl.rowIndex between(@pagesize*(@pageIndex-1)+1) and @pagesize*@pageIndex
end
--查询表中所有字段
create proc Pro_GetNews
as
select * from T_News
go
create proc Pro_GetNewsTitle
@newstitle varchar(64)
AS
SELECT NewsTitle,substring(NewsContent,1,20)+'...' as NewsContent,CreateTime from T_News
where NewsTitle like '%@newstitle%'
GO
CREATE proc Pro_GetMaxId
@maxid int out
as insert into T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId) values
('本周全国大部分地区降温','本周全国大部分地区降温,最高温度达10度','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5')
set @maxid=@@IDENTITY;
GO
DECLARE @maxid int;
EXEC Pro_GetMaxId @maxid out;
print @maxid
--接受用户输入的参数,插入数据,返回最大编号。
CREATE PROC Pro_GetMaxId1
@newstitle varchar(64),
@newscontent varchar(max),
@newscreator varchar(8),
@createtime DATETIME,
@classname varchar(30),
@maxid int out
as
DECLARE @classid uniqueidentifier;
select @classid=T1.ClassId from T_NewsClass as T1 WHERE T1.ClassName=@classname;
insert into T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId) values (@newstitle,@newscontent,@newscreator,GETDATE(),@classid)
set @maxid=@@IDENTITY;
GO
--执行存储过程:
declare @maxid int;
exec Pro_GetMaxId1 '111','222','003','2012-03-11','娱乐',@maxid out;
PRINT @maxid
GO