C#, SQL 2000, Stored Procedures, and a partridge in a pear tree 1

[AtomicChip]

OK, here's the scenario - I have 2 tables within a SQL 2000 database. Let's call them, aahh, Table1 and Table2. Table1 stores information about item batches received, and Table2 stores the detailed information about each batch received in Table1.

Now, I know that I _could_ do this an easier way by defining column ID's myself, but I don't want to. I would like to be able to do this by using SQL 2000's uniqueidentifier data type (isrowguid).

Now, here's what I would like to do:
Take user inputted fields, and stick them into Table1. From there, get the uniqueidentifier that was automagically generated and return that to the calling function. From there, I would like to take that returned ID, and input values into Table2 using that ID as a rowguid. I would like this to take place completely in a single stored procedure.

Any ideas? (This one's worth some _BIG_ stars ) -----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[Zarcom]

Here is the perfect solution...[perfect solution goes here] Sorry couldn't resist!

The members of this forum though many are familiar with SQL aren't near as good as the guys in the SQL forum. forum183

You'll get some magical answers from that place. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[cpastre]

If you want to wrap it in a stored procedure, I would do something like the following...

create proc insert_a_row @HeaderValue int, @DetailValue varchar(10), @row_id uniqueidentifier OUTPUT AS
select @row_id = newid()
insert Table1 (row_id, value1) values (@new_row_id, @HeaderValue)
insert Table2 (row_id, value2) values (@new_row_id, @DetailValue)

You can do exactly what you're asking if you use an IDENTITY field in combination with SELECT @@IDENTITY, but then you can't use the uniqueidentifier (guid) type. Because the uniqueidentifier is guaranteed to be unique, you can generate your own instead of keeping track of a next number or using an IDENTITY field. Use the Direction property on the SqlParameter object to specify the @row_id parameter as an output parameter when you execute the procedure using a SqlCommand.

Hope that helps,
Charlie

Charlie Pastre
MCSD, MCDBA, MCSE, CCEA, CCNA, CCDA