Accessing New INSERTED data 1

[tbode2006]

In my stored procedure I INSERT a record into table 1. Column C is defined as UNIQUEIDENTIFIER with default set as (newid()). I now want to use the new Column C value and INSERT 20 new records into table 2 with table 2 column A being table 1 column C value and table 2 column B be the values 1-20. My question is: in my stored procedure, how do I access the new UNIQUEIDENTIFIER value generated on the table 1 INSERT?

Table 1
A varchar(4)
B varchar(4)
C uniqueidentifier DEFAULT (newid)())

Table 2
A uniqueidentifier
B int

create procedure dbo.spADDTable1
(@a varchar(4),
@b varchar(4)) as

BEGIN TRANSACTION
INSERT into table1(A,B) VALUES(@a,@b)
COMMIT

Table1 Column C should now exist with a UNIQUEIDENTIFIER value

EXAMPLE DATA

Table 1
HELLO WORLD {F4074B47-78FB-447F-A5BA-0C1348EFDAF4}

Table 2
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 1
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 2
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 3
.
.
.
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 20

 

[AlexCuse]

I think you just need this?

[COLOR=blue]create[/color] [COLOR=blue]procedure[/color] dbo.spADDTable1
(@a [COLOR=blue]varchar[/color](4),
@b [COLOR=blue]varchar[/color](4)) [COLOR=blue]as[/color]

[COLOR=blue]BEGIN[/color] [COLOR=blue]TRANSACTION[/color]
[COLOR=blue]INSERT[/color] [COLOR=blue]into[/color] table1(A,B) [COLOR=blue]VALUES[/color](@a,@b)
[COLOR=blue]COMMIT[/color]

[b][COLOR=blue]select[/color] ID [COLOR=blue]from[/color] table1 [COLOR=blue]where[/color] a = @a and b = @b[/b]

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Instead of relying on the default for column C, you could get the NewId() and store it in a local variable. Like this...

[COLOR=blue]create[/color] [COLOR=blue]procedure[/color] dbo.spADDTable1
(@a [COLOR=blue]varchar[/color](4),
@b [COLOR=blue]varchar[/color](4)) [COLOR=blue]as[/color]

[COLOR=blue]BEGIN[/color] [COLOR=blue]TRANSACTION[/color]
[COLOR=blue]Declare[/color] @MyId UniqueIdentifier
[COLOR=blue]Set[/color] @MyId = NewId()

[COLOR=blue]INSERT[/color] [COLOR=blue]into[/color] table1(A,B,C) [COLOR=blue]VALUES[/color](@a,@b,@MyId)
[COLOR=blue]COMMIT[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

2000 or 2005? if you use 2005 take a look at the OUTPUT clause

CREATE TABLE TestOutput 
( 
ID uniqueidentifier NOT NULL default newID(), 
Description VARCHAR(50) NOT NULL, 
) 


DECLARE @MyTableVar table (
   ID uniqueidentifier
    )



INSERT INTO TestOutput (Description) 
OUTPUT Inserted.ID INTO @MyTableVar 
VALUES ('Desc4') 


DECLARE @id uniqueidentifier
SELECT @id = id FROM @MyTableVar

--you can now use this for the rest of your code
SELECT @id

However i would do what George suggested and remove the default

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[tbode2006]

Thanks for the quick replies. I think that I will eliminate the default and use George's suggestion.

This is SQL2K

 

[gmmastros]

Just to be clear here...

I wasn't necessarily suggesting that you remove the default for the GUID column. I was merely suggesting that you don't use the default for this query.

Since I don't know your database, it's possible that this default exists for other reasons, so removing may cause problems with other operations.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tbode2006]

The default wasn't really necessary in my table. I have the results that I need.

Thanks all