无标题1.sql

企业短信收发系统

if exists(select * from sysobjects where name='UserLogin')
drop procedure UserLogin
go
CREATE  procedure UserLogin
	@LoginName	varchar(20),
	@Password	varchar(20)
as
select * from UserInfo where LoginName=@LoginName
and Password=@Password
go

exec UserLogin 'admin','admin'

--添加用户--
if exists(select * from sysobjects where name='InsertUser')
drop procedure InsertUser
go
create procedure InsertUser
@LoginName  varchar(20),
@Password varchar(20),
@UserName varchar(20),
@Gender varchar(20),
@Moblie varchar(20),
@Remark varchar(256)
as
insert into UserInfo values(@LoginName,@Password,@UserName,@Gender,@Moblie,@Remark)
go
exec InsertUser 'aaa','123','aaa','1','111111','dasfa'


--用户信息--
if exists(select * from sysobjects where name='AllUserInfo')
drop procedure AllUserInfo
go
create procedure AllUserInfo
as
select * from UserInfo
go
exec AllUserInfo




--创建地址本--
if exists(select * from sysobjects where name='IsertAddressBook')
drop procedure IsertAddressBook
go
create procedure IsertAddressBook
@OwnerUserId int,
@AdderssBookName varchar(50),
@Remark varchar(256)
as
insert into AddressBook values(@OwnerUserId,@AdderssBookName,@Remark)
return @@identity
go


--添加AddBookDetail--
if exists(select * from sysobjects where name='InsertAddBookDetail')
drop procedure InsertAddBookDetail
go
create procedure InsertAddBookDetail
@AddressBookId int,
@Contacterid int
as
insert into AddressBookDetail values(@AddressBookId,@Contacterid)

exec InsertAddBookDetail 1,1

delete from AddressBookDetail




--查询地址本名称--

if exists(select * from sysobjects where name='SelectAddBookName')
drop procedure SelectAddBookName
go
create procedure SelectAddBookName
@OwnerUserId int,
@AddressBookName varchar(50)=null
as
declare @sql as varchar(1024)
set @sql='select AddressBookName,AddressBookId from AddressBook where OwnerUserId='
+ convert(varchar(8),@OwnerUserId)
if(@AddressBookName=null)

set @sql=@sql

if(@AddressBookName  is not null)

set @sql=@sql+ 'and AddressBookName =''' + @AddressBookName + ''''

print @sql
exec (@sql)
go
exec SelectAddBookName 1,null




--查询地址本详细信息--
if exists(select * from sysobjects where name='SelectAddBookInfo')
drop procedure SelectAddBookInfo
go
create procedure SelectAddBookInfo
@AddressBookId int
as
select UserId,LoginName,UserName,Gender,Mobile,@AddressBookId as AddressBookId 
from UserInfo where UserId in
(select Contacterid from AddressBookDetail where AddressBookId=@AddressBookId)

go

exec SelectAddBookInfo 1

--删除地址本中信息--
if exists(select * from sysobjects where name='DeleteAddressBookUser')
drop procedure DeleteAddressBookUser
go
create procedure DeleteAddressBookUser
@AddressBookId int,
@UserId int
as
delete from AddressBookDetail where AddressBookId=@AddressBookId and 
Contacterid=@UserId
go

exec DeleteAddressBookUser 9,1   


--删除地址本--
if exists(select * from sysobjects where name='DeleteAddressBook')
drop procedure DeleteAddressBook
go
create procedure DeleteAddressBook
@AddressBookId int
as

delete from AddressBook where AddressBookId=@AddressBookId
delete from AddressBookDetail where AddressBookId=@AddressBookId
go

exec DeleteAddressBook 7



--发送信息--
if exists(select * from sysobjects where name='InsertSendMessage')
drop procedure InsertSendMessage
go
create procedure InsertSendMessage
@SendUserId int,
@Message varchar(1024)
as
insert into Outbox values(@SendUserId,getdate(),@Message,1,1)
go

exec InsertSendMessage 1,'1212121212'

--发送详细信息--
if exists(select * from sysobjects where name='InsertSendMessageDetail')
drop procedure InsertSendMessageDetail
go
create procedure InsertSendMessageDetail
@ReceiverId int
as
insert into OutBoxDetail(OutBoxId,ReceiverId) (select max(OutBoxId),@ReceiverId from Outbox)

go

--获得已发送信息--
if exists(select * from sysobjects where name='SelectSendMessage')
drop procedure SelectSendMessage
go
create procedure SelectSendMessage
@SendUserId int

as
select OutBoxId,SendTime,Status,Message from OutBox where SendUserId=@SendUserId
go

exec SelectSendMessage 1

--获得已发送详细信息--
if exists(select * from sysobjects where name='SelectMessageDetail')
drop procedure SelectMessageDetail
go
create procedure SelectMessageDetail
@OutBoxId int
as

select * from UserInfo where userId in(select ReceiverId from OutBoxDetail where OutBoxId = @OutBoxId)
go

exec SelectMessageDetail 34


--删除已发送信息--
if exists(select * from sysobjects where name='DelSendMessage')
drop procedure DelSendMessage
go
create procedure DelSendMessage
@OutBoxId int
as
delete from Outbox where OutBoxId=@OutBoxId
delete from OutBoxDetail where OutBoxId=@OutBoxId
go



--接收信息--
if exists (select * from sysobjects where name='InsertInboxMessage')
drop procedure InsertInboxMessage
go
create procedure InsertInboxMessage
@SendUserId int,
@Message varchar(1024),
@ReceiverId int
as
insert into inBox values(@SendUserId,@ReceiverId,getdate(),0,0,@Message)
go

--未读信息--
if exists (select * from sysobjects where name='SelectUnReadMessage')
drop procedure SelectUnReadMessage
go
create procedure SelectUnReadMessage
@ReceiveUserId int
as
select (select UserName from UserInfo where UserId=SendUserId)as SendUserId,ReceiveTime,Status,Message,InBoxId
 from Inbox where status=0 and ReceiveUserId=@ReceiveUserId
go

--所有信息--
if exists (select * from sysobjects where name='SelectAllMessage')
drop procedure SelectAllMessage
go
create procedure SelectAllMessage
@ReceiveUserId int
as
select Status,(select UserName from UserInfo where UserId=SendUserId)as SendUserId,ReceiveTime,Status,Message,InBoxId
 from Inbox where ReceiveUserId=@ReceiveUserId
go


exec SelectUnReadMessage 2

--修改信息状态（是否已读）--
if exists (select * from sysobjects where name='UpdateMessageStatus')
drop procedure UpdateMessageStatus
go
create procedure UpdateMessageStatus
@InBoxId int
as
update InBox set Status=1 where InBoxId=@InBoxId
go