Sql Server 存储过程

用户的增删改查

新增

--新增用户
create procedure addDeviceUser
	@name nvarchar(36) ,
	@phone nvarchar(36) ,
	@password nvarchar(255)
as
begin
	if exists(select 1 from DeviceUser where phone=@phone) --判断用户是否存在	
		select 1 from DeviceUser where 1!=1
	else
		begin
			insert into DeviceUser (id,name,phone,password) values(newid(),@name,@phone,@password)
			select id,name,phone from DeviceUser where phone=@phone
		end
end

--执行
exec addDeviceUser @name='testname',@phone='18800000000',@password='123'

查询

--获取用户
alter procedure getDeviceUser
	@id nvarchar(36),
	@phone nvarchar(36)
as
begin
	if(@id=''and @phone='')
		select id,name,phone,isAdmin,createDate from DeviceUser where isAdmin=0 order by createDate desc
	else if(@id!='')
		select id,name,phone,isAdmin,createDate from DeviceUser where id=@id
	else
		select id,name,phone,isAdmin,createDate from DeviceUser where phone=@phone
end

--执行
exec getDeviceUser @id='', @phone=''
exec getDeviceUser @id='test guid', @phone=''
exec getDeviceUser @id='', @phone='18800000000'


--登录
create procedure getDeviceUserForLogin
	@phone nvarchar(36),
	@password nvarchar(255)
as
begin
	select id,name,phone,isAdmin,createDate from DeviceUser where phone=@phone and password=@password 
end

exec getDeviceUserForLogin @phone='18800000000',@password='123'
exec getDeviceUserForLogin @phone='18800000000',@password='123456'
exec getDeviceUserForLogin @phone='18811111111',@password='123456'

更新

create procedure updateDeviceUser
	@id nvarchar(36) ,
	@name nvarchar(36) ,
	@phone nvarchar(36) ,
	@password nvarchar(255)
as
begin
	if not exists(select 1 from DeviceUser where id=@id) --判断用户是否存在	
		select 1 from DeviceUser where 1!=1
	else
		begin
			update DeviceUser set name=@name,phone=@phone,password=@password  where id=@id
			select id,name,phone from DeviceUser where id=@id
		end
end
 
--执行
exec updateDeviceUser @id='guid 1',@name='test name 1',@phone='18800000000',@password='123'
exec updateDeviceUser @id='guid 2',@name='test name 2',@phone='18811111111',@password='12345678'

删除

create procedure deleteDeviceUser
	@id nvarchar(36)
as
delete from DeviceUser  where id=@id