procedure

ALTER PROCEDURE bargaining.pro_wupinxinxiSplit
(@xinxitype varchar(50))
AS
if(@xinxitype='1')    
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where shengyushuliang>0 and xinxileixing='出售' order by shangpinid desc
if(@xinxitype='2') 
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where shengyushuliang>0 and xinxileixing='求购' order by shangpinid desc
if(@xinxitype='0')
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where shengyushuliang>0  order by shangpinid desc

======================================================================

ALTER PROCEDURE pro_aa
@gamename varchar(50)
AS
insert into game(gamename) values(@gamename)
============================================

ALTER PROCEDURE pro_addUsers
(@username varchar(50),@passwords varchar(50),@paymentword varchar(50),@email varchar(50),@accountname varchar(50),@accountid varchar(50),@qq int,@Hometelephone varchar(50),@telephone varchar(50),@nickname varchar(50),@sex varchar(2),@identityid varchar(50),@sheng varchar(50),@city varchar(50),@dizhi varchar(100),@youbian int,@bankanme varchar(100))
AS
declare @identity int,@myerror int
begin tran
insert into users(username,passwords,paymentword,email) values(@username,@passwords,@paymentword,@email)
select @identity=@@identity
if(@identity>0)
insert into detailedinformation values(@identity,@accountname,@accountid,@qq,@Hometelephone,@telephone,@nickname,@sex,@identityid,@sheng,@city,@dizhi,@youbian,@bankanme)
else
rollback tran

set @myerror=@@Error
if(@myerror=0)
commit transaction
else
rollback transaction

======================================================

ALTER PROCEDURE pro_addWupin
(@userid int,@propid int,@gameid int,@subareaid int,@serverid int,@xinxileixing varchar(50),@xianjia money,@shuliang int,@furthest int,@youxiaotime int,@wupinmiaoshu varchar(1000),@xinxibiaoti varchar(50),@fabutime varchar(50),@clientid int)
AS
declare @wupinerror int,@myerror int
begin tran
insert into wupinxinxi(userid,propid,gameid,subareaid,serverid,xinxileixing,xianjia,shuliang,furthest,shengyushuliang,youxiaotime,wupinmiaoshu,xinxibiaoti,fabutime,clientid)
values(@userid,@propid,@gameid,@subareaid,@serverid,@xinxileixing,@xianjia,@shuliang,@furthest,@shuliang,@youxiaotime,@wupinmiaoshu,@xinxibiaoti,@fabutime,@clientid)
set @wupinerror=@@Error
update client set zhuangtai='处理交易中',renwushu=renwushu+1 where clientid=@clientid
set @myerror=@@Error
if(@wupinerror=0 and @myerror=0)
commit transaction
else
rollback transaction

====================================================

ALTER PROCEDURE pro_addyouxibidingdan
(@userid int,@wupingname varchar(500),@jiage money,@dingdantime varchar(100),@clientid int,@number int,@xiangxiid int)
AS
declare @erra int,@errb int
begin tran
insert into bidingdan values(@userid,'游戏币',@wupingname,@jiage,@dingdantime,@clientid,@number,'未处理',@xiangxiid,0)
set @erra=@@Error
update client set zhuangtai='处理交易中',renwushu=renwushu+1 where clientid=@clientid
set @errb=@@Error
if(@erra=0 and @errb=0)
commit transaction
else
rollback transaction

===================================================

ALTER PROCEDURE pro_diankatran
(@userid int,@wupinname varchar(100), @jiage money, @dingdantime varchar(50), @clientid int = 0,
@number int = 1,@mymoney money,@diankaid int)
AS
declare @erra int,@errb int,@errc int
begin tran
INSERT INTO dingdan VALUES (@userid,'点卡' ,@wupinname, @jiage,@dingdantime, @clientid, @number,0)
set @erra=@@Error
 
update account set zhongamount=zhongamount-@mymoney,keyongamount=keyongamount-@mymoney,goumaijiao=goumaijiao+1,buyshu=buyshu+1,jiaoyimoney=jiaoyimoney+@mymoney where userid=@userid
set @errb=@@Error
delete from dianka where diankaid=@diankaid
set @errc=@@Error
if(@erra=0 and @errb=0 and @errc=0)
commit tran
else
rollback transaction

=========================================================

ALTER PROCEDURE pro_group
AS
SELECT clientid,cname,renwushu
FROM client
where shangxian=1
ORDER BY renwushu

==========================================================

ALTER PROCEDURE pro_insertdingdan
(@userid int, @propid int=0,@wupinname varchar(100), @jiage money, @dingdantime varchar(50), @clientid int = 0,
@number int = 1)
AS
declare @propname varchar(50)
if @propid=0
set @propname='点卡'
else
set @propname= (select propname from prop where propid=@propid)
 INSERT INTO dingdan VALUES (@userid,@propname ,@wupinname, @jiage,@dingdantime, @clientid, @number)

=======================================================================

ALTER PROCEDURE pro_insertKefuguanli
(@clientid int ,@chushouid int,@goumaiid int,@jiaoyitime smalldatetime,@jiaoyiwupin varchar(50),@jiaoyimoney money,@shangpinid int,@shuliang int)
AS
declare @erra int,@errb int
begin tran
insert into kefuguanli(clientid,chushouid,goumaiid,jiaoyitime,jiaoyiwupin,jiaoyimoney,shangpinid,shuliang) values(@clientid,@chushouid,@goumaiid,@jiaoyitime,@jiaoyiwupin,@jiaoyimoney,@shangpinid,@shuliang)
set @erra=@@Error
update wupinxinxi set dingdan=1 where shangpinid=@shangpinid
set @errb=@@Error
if(@erra=0 and @errb=0)
commit tran
else
rollback tran

==========================================================

ALTER PROCEDURE pro_jionwupinxinxi
(@shangpinid int)
AS
select u.userid,u.username,u.email,p.propname,p.propid,g.gamename,s.gamesubarea,se.gameserver,w.xinxileixing,w.jiage,w.xianjia,w.shuliang,w.furthest,w.shengyushuliang,w.youxiaotime,w.wupinmiaoshu,w.xinxibiaoti,w.fabutime,w.clientid from wupinxinxi w join users u on w.userid=u.userid join prop p on w.propid=p.propid join game g on g.gameid=w.gameid join server se on se.serverid= w.serverid join subarea s on s.subareaid=w.subareaid where w.shangpinid=@shangpinid

============================================================

ALTER PROCEDURE pro_selectAccountByUserid
(@userid int)
AS
select * from account where userid=@userid

==========================================================

ALTER PROCEDURE dbo.pro_selectClient
AS
select c.cname,c.qq,c.zhuangtai,min(k.jiaoyitime)as jiaoyitime,c.shangxian from client c left outer join kefuguanli k on c.clientid=k.clientid group by c.cname,c.zhuangtai,c.shangxian,c.qq  having shangxian=1order by c.shangxian desc

============================================================

ALTER PROCEDURE pro_selectgerenxinxi
(@userid int)
AS
select u.passwords,u.paymentword,u.email,d.accountname,d.accountid,d.QQ,d.telephone,d.nickname,d.bankname from users u join detailedinformation d on u.userid=d.userid where u.userid=@userid

=================================================================

ALTER PROCEDURE pro_selectUsersByEmail
(@email varchar(50),@username varchar(50))
AS
select * from users where username=@username and email=@email

=================================================================

ALTER PROCEDURE pro_selectUsersByUsername
(@username varchar(20),@passwords varchar(20))
AS
select * from users where username=@username and passwords=@passwords

===================================================================

ALTER PROCEDURE pro_updateAccount
(@userid int,@mymoney money)
AS
update account set zhongamount=zhongamount-@mymoney,keyongamount=keyongamount-@mymoney,goumaijiao=goumaijiao+1,buyshu=buyshu+1,jiaoyimoney=jiaoyimoney+@mymoney where userid=@userid

================================================================

ALTER PROCEDURE pro_updategerenxinxi
(@passwords varchar(50),@paymentword varchar(50),@email varchar(50),@accountname varchar(50),@accountid varchar(50),@qq int,@telephone varchar(50),@nickname varchar(50),@bankname varchar(50),@userid int)
AS
declare @userserror int,@detaerror int
begin tran
update users set passwords=@passwords,paymentword=@paymentword,email=@email where userid=@userid
set @userserror=@@Error
update detailedinformation set accountname=@accountname,accountid=@accountid,qq=@qq,telephone=@telephone,nickname=@nickname,bankname=@bankname where userid=@userid
set @detaerror=@@Error
if(@userserror=0  and  @detaerror=0)
commit transaction
else
rollback transaction

 

=============================================================

ALTER PROC pro_updatetime AS UPDATE wupinxinxi SET youxiaotime = youxiaotime - 1

=======================================

ALTER PROCEDURE pro_wupinxinxiSplit
(@xinxitype varchar(50))
AS
if(@xinxitype='1')    
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where dingdan=0 and xinxileixing='出售' order by fabutime desc
if(@xinxitype='2') 
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where dingdan=0 and xinxileixing='求购' order by fabutime desc
if(@xinxitype='0')
select shangpinid,propname,gamename,gamesubarea,gameserver,xinxileixing,jiage,xianjia,shuliang,shengyushuliang,youxiaotime,xinxibiaoti from wupinxinxi  wu join prop p on wu.propid=p.propid join game g on wu.gameid=g.gameid join subarea s on wu.subareaid=s.subareaid join server se on wu.serverid=se.serverid where dingdan=0 order by fabutime desc

==========================================================

ALTER PROCEDURE pro_xinxifabuSplit
(@pagerow int,@pageno int)
AS
declare @sql varchar(500)
set @sql='select top '+cast(@pagerow as varchar(20))+' * from (select top '+cast((@pagerow*@pageno)as varchar(20))+' * from xinxifabu) as xinxi'
print @sql
exec (@sql)

========================================================

ALTER proc yanzheng
@yonghu varchar(50)
as
select username from users
where username=@yonghu
===============================================