asp.net sql存储过程(3)


/*
作者:德仔
用途:查询sellinfo里所有的记录
日期:2006-3-23
*/
create procedure sellinfo_select
as
select * from sellinfo
GO


(2) 删除指定的ID记录

程序代码:

复制代码 代码如下:


/*
作者:德仔
用途:删除sellinfo里由输入参数@sell_id指定的ID记录
日期:2006-3-23
*/
CREATE PROCEDURE sellinfo_delete
@sell_id bigint
as
delete from [sellinfo]
where
sell_id=@sell_id
GO


(3)更新所对应的记录

程序代码:

复制代码 代码如下:


/*
作者:德仔
用途:修改相对应的小类名
日期:2006-4-5
*/
create procedure prosmallclass_update_id
@smallid int,
@smallname char(50)
as
update [ProductCats]
set
PdtCat_Name = @smallname
where
PdtCat_id =@smallid
GO


(4)验证登陆

程序代码:

复制代码 代码如下:


/*
作者:德仔
用途:通过得到的@user_name @user_password验证登陆
日期:2006-3-21
*/
CREATE procedure user_login
@user_name varchar(50),
@user_password varchar(50)
as
select * from usercompany where [User_Name] = @User_Name and [User_Pwd] = @User_Password
if @@rowcount>0
begin
update [users] set user_LoginTimes=user_LoginTimes+1 where [User_Name] = @User_Name and [User_Pwd] = @User_Password
end
GO


(5)密码修改

程序代码:

复制代码 代码如下:


/*
作者:德仔
用途:先查到user的密码,再修改新密码
日期:2006-3-23
*/
create procedure user_pwd
@user_name varchar(30),
@user_oldpwd varchar(30),
@user_newpwd varchar(30),
@iOutput int output
as
if exists(select * from users where User_Name=@user_name and user_pwd=@user_oldpwd)
begin
update users set user_pwd=@user_newpwd where User_Name=@user_name and user_pwd=@user_oldpwd
set @iOutput = 1
end
else
set @ioutput = -1
GO


(6)增加新记录

程序代码:

复制代码 代码如下:


/*
作者:德仔
用途:添加一条新留言
日期:2006-4-8
*/
CREATE procedure gb_add
@gbusername char(50),
@gbusermemberid char(50),
@gbuseremail char(50),
@gbusersubject char(50),
@gbusercontent char(1500)
as
insert gb
(
gbusername,
gbusermemberid,
gbuseremail,
gbsubject,
gbcontent
)
values
(
@gbusername,
@gbusermemberid,
@gbuseremail,
@gbusersubject,
@gbusercontent
)
GO


(7)统计数据

程序代码: [ 复制代码到剪贴板 ]

复制代码 代码如下:


/*
作者:德仔
用途:用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数
日期:2006-3-23
*/
CREATE procedure datacount
as
declare @MemberCount int
declare @MemberVip int
declare @MemberNorm int
declare @MemberUnchkReg int
declare @MemberLblRegChk int
declare @CompanyCount int
declare @CompanyRcmd int
declare @SellCount int
declare @SellRcmd int
declare @SellUnchk int
declare @SellChk int
declare @CountSupply int
declare @SupplyRcmd int
declare @SupplyUnchk int
declare @SupplyChk int
declare @NewsCount int
declare @NewsRcmd int
declare @NewsClassCount int
declare @SupplyClass int
declare @SellClass int
declare @MsgCount int
declare @ProBigclass int
declare @proSmallclass int
select @MemberCount= count(User_Id)from Users
select @MemberVip=count(User_Id)from Users where User_Level =2
select @MemberNorm=count(User_Id)from Users where User_Level =1
select @MemberUnchkReg=count(user_id) from users where user_IsChecked=0
select @MemberLblRegChk=count(user_id) from users where user_IsChecked=1
select @CompanyCount=count(COM_id) from Company
select @CompanyRcmd=count(COM_id) from Company where COM_IsRcmd=1
select @SellCount =count(Sell_Id) from sellinfo
select @SellRcmd =count(Sell_Id) from sellinfo where Sell_IsRcmd=1
select @SellUnchk =count(Sell_Id) from sellinfo where Sell_Ischecked = 0
select @SellChk =count(Sell_Id) from sellinfo where Sell_Ischecked = 1
select @CountSupply =count(Supply_Id)from supplyInfo
select @SupplyRcmd =count(Supply_Id)from supplyInfo where Supply_Isrcmd=1
select @SupplyUnchk =count(Supply_Id)from supplyInfo where Supply_Ischecked=0
select @SupplyChk =count(Supply_Id)from supplyInfo where Supply_Ischecked=1
select @NewsCount =count(news_id) from news
select @NewsRcmd =count(news_id) from news where News_Recommand=1
select @NewsClassCount =count(news_id) from news
select @proBigclass = count(PdtCat_SortId) from productcats where PdtCat_SortId=0
select @proSmallClass = count(PdtCat_SortId)from productcats where PdtCat_SortId<>0
select @MsgCount = count(Msg_id) from MSg
select
MemberCount=@MemberCount,
MemberVip=@MemberVip,
MemberNorm=@MemberNorm,
MemberUnchkReg=@MemberUnchkReg,
MemberLblRegChk=@MemberLblRegChk,
CompanyCount=@CompanyCount,
CompanyRcmd=@CompanyRcmd,
SellCount=@SellCount,
SellRcmd=@SellRcmd,
SellUnchk=@SellUnchk,
SellChk=@SellChk,
CountSupply =@CountSupply,
SupplyRcmd =@SupplyRcmd,
SupplyUnchk=@SupplyUnchk,
SupplyChk =@SupplyChk,
NewsCount=@NewsCount,
NewsRcmd=@NewsRcmd,
NewsClassCount=@NewsClassCount,
probigclass=@probigclass,
prosmallclass=@prosmallclass,
MsgCount = @MsgCount
GO


(8)模糊查询

程序代码:

复制代码 代码如下:

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wjjpfs.html