SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 how to insert in nested tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 03/06/2014 :  04:19:34  Show Profile  Reply with Quote

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
???? ???? ???????

khtan
In (Som, Ni, Yak)

Singapore
17655 Posts

Posted - 03/06/2014 :  04:34:38  Show Profile  Reply with Quote
Should your first and second INSERT statement swapped ?

As your 2nd IF will not execute. You checks for none-existence of record in OFFICE table when you have just inserted a record earlier.




KH
Time is always against us

Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 03/06/2014 :  09:28:04  Show Profile  Reply with Quote
1---1 means one to one one to one relationship.
(members[id,name]-lecturer[id,name]-office[id,name] - users[id,username,type,fk_table] -contact[fk_user,email,phone] )tables
there is relation between users 1---1 contact(fk_user).
{members,lecturer,office} 1---1 users(fk_table)
my qeution is,how to biuld relation between {members,lecturer,office} and users(fk_table)?
i can not biuld relation between three PK and one fk


http://aman-services.net
for office
???? ???? ???????
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000