July 17, 2003 at 2:20 am
Hi,
I am using UNIQUEIDENTIFIER column for my table. I do not insert data for it. I left it on database by setting to default NEWID(). Now in my application I need the value of UNIQUEIDENTIFIER column I just inserted. Is there any function or query to get this value like in case of IDENTITY column we can get the latest inserted value from select @@IDENTITY.
Thanks and regards,
Uday
July 17, 2003 at 2:24 am
Select ColName From TableName Where [IdentityColName] = @@Identity
ColName is the name of the column with the GUID and IdentityColName is your IdentityCOlumn.
Cheers,
Crispin
Something as incredibly simple as
binary still gives you too many options
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 17, 2003 at 2:37 am
Hi Crispin,
Thank you for the consideration.
I do not have any identity column in my table. I think I have mis-leaded you. Actually my question was, does we have any means to get newly inserted value of uniqueidentifier column which is inserted by database from the function NEWID(). My application doesn't know what value is inserted at back-end.
Thanks,
Uday
Edited by - udayt on 07/17/2003 02:40:54 AM
July 17, 2003 at 2:40 am
Not as far as I know. The easiest way would then be to declare a local variable in the proc and set it to be a newID and insert that value and select it out of the proc.
I doubt there are functions for getting last inserted default values.
Cheers,
Crispin
Something as incredibly simple as
binary still gives you too many options
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 17, 2003 at 4:38 am
For most situations the best thing I have found is to wrap the insert logic into an SP with and output variable like so.
CREATE PROC ip_DoInsert
@col1 datatype,
@col2 datatype,
...,
@newid uniqueidentifier OUTPUT
AS
SET NOCOUNT ON
SET @newid = NEWID()
INSERT tblX
(collist)
VALUES
(variblelistincluding@newid)
GO
Then using the ADO command parameters you can retrieve the output variable which is your newid.
July 17, 2003 at 5:06 am
Agree. The other alternative (and a huge advantage in my opinion) is that if you need the key back, you can generate it on the client and avoid the round trip altogether.
Andy
July 18, 2003 at 2:38 am
if you don't want to wrap your insert logic into a stored procedure, generate your GUID directly on client side and insert it into your client side recordset (rowguidcol attribute does not prevent this!).
Eg. in C/C++ by directly using CoCreateGuid, in Delphi by using CreateClassId or in VB a little bit more complicated, like follows:
http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=23
best regards,
chris
July 18, 2003 at 6:37 am
fyi, btw...
just got done w/a custom replication engine written in java - listen to the wizards that tell you to create it on the client...!
July 18, 2003 at 7:03 am
Actually, I do most of my stuff on the server in a sp. I have a timestamp value in every table I use to prevent any overwriting of data so I call the stored procedure to insert a record and it creates the unique id and return the entire record back (including timestamp).
What development environment are you using?
July 18, 2003 at 7:05 am
we're using java swing client/server and sql 2k....
i guess the reason i say do it on the client is that you can create the uniqueid and then reference it immediately before inserting a new row - just like what's-his-name says...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply