ALTER PROCEDURE [dbo].[new_office]
(
------------basic office info-------------
@name nvarchar(150),
@nameManger nvarchar(50),
@permit_id nchar(10),
@type_work nvarchar(50),
@create_Date date,
@location nvarchar(300)='no location',
@fk_nieghborehood int,
@fk_org int,
-------------info username-------
@username nvarchar(50),
@password nchar(20),
-----------------info contact------
@email nvarchar(50),
@mobile nchar(10),
@phone nchar(10)='no',
@facebook nvarchar(200)='no',
@twitter nvarchar(200)='no',
@website nvarchar(200)='no',
--------------message----------
@msg nvarchar(50) output
)
AS
BEGIN
if not exists(select @username from users where username=@username)
begin
declare @time datetime2= sysdatetimeoffset()
insert into office(name,nameManger,permit_id,type_work,create_Date,map_location,date_register,fk_nieghborehood,fk_org)
values(
@name,
@nameManger,
@permit_id,
@type_work,
@create_Date,
@location,
@time,
@fk_nieghborehood,
@fk_org
)
------------second if-----------
if not exists(select id from office where date_register=@time)
begin
insert into users(user_id,username,password,type)
values
(
(select id from office where date_register=@time),
@username,
@password,
'office'
)
insert into contact(id,email,mobile,phone,facebook,twitter,website)
values
(
(select users.id from users,office where date_register=@time and user_id=office.id and users.type='office'),
@email ,
@mobile ,
@phone ,
@facebook ,
@twitter ,
@website
)
end
else
begin
set @msg='fail create username for office'
end
end
else
begin
set @msg ='sorry, this user not allow'
end
END
http://aman-services.net
for office
???? ???? ???????