inserting the same uniqueidentiifier in to two tables using two stored procedures

Andile Choko

15y2.5k

Hi,

I have created two tables:

create table dawie
(
Id uniqueIdentifier default newId() primary key,
name varchar(25)
)

create table chk
(
guid uniqueIdentifier primary key,
name varchar(10),
id uniqueIdentifier foreign key references dawie(Id)
)

Now how do i insert into both tables using stored procedures?

the previous solution worked, thanx.

How do i do the same using 2 stored procedures,i.e.

create proc insert_into_chk
(
)

create proc insert_into_ dawie
(
)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs15y ago
Andile Choko might have unselected the answer but if so then it would help to know why. I know you would try to help if you knew that there was a problem.
Guest User15y ago
I got an email saying that my answer had been accepted, but it's not showing as such here... and I didn't get the points...???
Guest User15y ago
insert_into_dawie can use an output parameter to return the new id:

CREATE PROCEDURE insert_into_dawie
(@i_Name varchar(25), @o_newdawieid uniqueidentifier output)
AS

DECLARE @DawieID uniqueidentifier
SET @o_newdawieid = newid()

insert into dawie (Id, name) values (@o_newdawieid, @i_Name)

GO

then you can pass that new id to insert_into_chk so that you maintain the fk relationship:

create procedure insert_into_chk
(@i_Name varchar(10), @i_dawieid uniqueidentifier)
as

declare @chkid uniqueidentifier
set @chkid = newid()

insert into chk ([guid], name, id) values (@chkid, @i_Name, @i_dawieid)
go

Here is an example of how you can call the procs:

DECLARE @i_newdawieid uniqueidentifier
exec insert_into_dawie @i_Name='Foo', @o_newdawieid = @i_newdawieid output
exec insert_into_chk @i_Name='Foo2', @i_dawieid = @i_newdawieid

The call to insert_into_dawie returns the new id into @i_newdawieid, which is then passed to insert_into_chk.