9

Goal

Retrieve the latest guid value in real time after you have inserted the value in the table

Problem

Don't know how to do it

Info

  • The code should only specify new values for address and zipcode
  • There can be lots of data in the table

Table

CREATE TABLE [AddressBook]
(
    [testID] [uniqueidentifier] NOT NULL default newid(),
    [address] [nvarchar](50) NULL,
    [zipcode] [nvarchar](50) NULL
)
  • 1
    I would suggest using NEWSEQUENTIALID() rather than NEWID(), otherwise your clustered index (assuming it's going to be on testID) will become heavily fragmented. – Jonathan Fite Jul 25 '17 at 13:59
13

I think you are looking for output

DECLARE @MyTableVar table([testID] [uniqueidentifier]);
 INSERT [AddressBook] ([address], [zipcode])
        OUTPUT INSERTED.[testID] INTO @MyTableVar
 VALUES (N'address', N'zipcode');

--Display the result set of the table variable.
 SELECT [testID] FROM @MyTableVar;

GO

uniqueidentifier may not be the most efficient id here but this is an answer to the stated question

  • note that INSERTED.[testID] is the property of what you want from the row, not the name of the property in @MyTableVar – smurtagh Aug 12 '19 at 15:08
-1
CREATE TABLE [dbo].[tbl_Clients](
    [ClientID] [uniqueidentifier] NULL,
    [ClientName] [varchar](250) NULL,
    [ClientEnabled] [bit] NULL
) ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[sp_ClientCreate]
@in_ClientName varchar(250) = "New Client 123",
@in_ClientEnabled bit,
@out_ClientId uniqueidentifier OUTPUT
AS

SET @out_ClientId = NEWID();

INSERT
INTO tbl_Clients(ClientId, ClientName, ClientEnabled) 
VALUES(
@out_ClientId, 
@in_ClientName,
@in_ClientEnabled)


DECLARE @return_value int,
        @out_ClientId uniqueidentifier

EXEC    @return_value = [dbo].[sp_ClientCreate]
        @in_ClientName = N'111',
        @in_ClientEnabled = 1,
        @out_ClientId = @out_ClientId OUTPUT

SELECT  @out_ClientId as N'@out_ClientId'

SELECT  'Return Value' = @return_value

GO 

Result:-59A6D7FE-8C9A-4ED3-8FC6-31A989CCC8DB

  • The question asks how to get the value generated by the default constraint. This doesn't answer that question, even though it does provide a workaround – Max Vernon Jul 3 '18 at 18:32
-4

Another way,

DECLARE @id varchar(50) = CONVERT(VARCHAR(50), NEWID());

INSERT INTO [yourtable]
( [id])
VALUES
(@id);

SELECT @id;
  • The question asks how to get the value generated by the default constraint. This doesn't answer that question, even though it does provide a workaround. – Max Vernon Jul 3 '18 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.