Get an UID from a stored procedure

Hello,
UID in the table is the auto generated field.
How can i retrieve the new generated UID in the table after an Insert query for that record.
 INSERT INTO DIY
		(
			[Title],
			[Name],
				
				
		) 
		VALUES
		(
			CAST(@Title AS  Nvarchar(10)),
			CAST(@Name AS NVARCHAR(50)),	
		   

'Need to get the UID 



INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''''		
		
			
		)
    

Open in new window



Regards
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dorababu MSenior Software EngineerCommented:
UID is auto generated means is it a Identity column? If so you can use any of these

Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('TABLE_DEBUG')

Open in new window

0
Dorababu MSenior Software EngineerCommented:
You can use this way too

INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
[Title], [Name], [UID])  Output Inserted.UID
VALUES
(
	'''+@Title + ''' ,		
	'''+@Name +''' ,
	'''+ @UID+''''		
)

Open in new window


Example fiddle

http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/18086
0
RIASAuthor Commented:
Thanks will try and brb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RIASAuthor Commented:
Thanks but I am getting an error
undeclared  @UID
0
Dorababu MSenior Software EngineerCommented:
You are passing it in your query see your post and correct accordingly.
0
RIASAuthor Commented:
I am passing the returned UID     , it was just an example on how I need to use the UID.
0
Dorababu MSenior Software EngineerCommented:
Then in that case you can ignore from insert statement if it is an auto identity field
0
RIASAuthor Commented:
How can I use the UID in the query?
0
RIASAuthor Commented:
I need to insert the UID in this query

INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''''		
		
			
		)

Open in new window

0
Dorababu MSenior Software EngineerCommented:
Is UID auto generated field?
0
RIASAuthor Commented:
Yup
0
RIASAuthor Commented:
It is     uniqueidentifier
0
RIASAuthor Commented:
I insert my record by this query
 INSERT INTO DIY
		(
			[Title],
			[Name],
				
				
		) 
		VALUES
		(
			CAST(@Title AS  Nvarchar(10)),
			CAST(@Name AS NVARCHAR(50)),	

Open in new window

0
Dorababu MSenior Software EngineerCommented:
In which table it is identity field?
0
RIASAuthor Commented:
in table DIY
0
RIASAuthor Commented:
I then need to get the UID and insert into debug table:

INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''''		
		
			
		)

Open in new window

0
RIASAuthor Commented:
It is an stored procedure with two queries.
0
Dorababu MSenior Software EngineerCommented:
DECLARE @uid int;

INSERT INTO DIY
(
	[Title],
	[Name]
) 
VALUES
(
	CAST(@Title AS  Nvarchar(10)),
	CAST(@Name AS NVARCHAR(50))
)

SET @uid  = @@IDENTITY 

Open in new window

0
RIASAuthor Commented:
Thanks, will try and brb
0
RIASAuthor Commented:
Thanks but, no value is inserted in the debug table for UID
0
RIASAuthor Commented:
  Declare   @UID Nvarchar(MAX)=null

INSERT INTO DIY
		(
			[Title],
			[Name],
				
				
		) 
		VALUES
		(
			CAST(@Title AS  Nvarchar(10)),
			CAST(@Name AS NVARCHAR(50)),	
		   



  SET @UID  = @@IDENTITY



INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''' ,		
		
			
		)
    

Open in new window

0
Dorababu MSenior Software EngineerCommented:
can you show your table syntax of DIY
0
Mark WillsTopic AdvisorCommented:
Ummm... thought IDENTITY was different to GUID

try SELECT NEWID()

See : https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql
0
RIASAuthor Commented:
Thanks , Will try and brb
0
RIASAuthor Commented:
Marks,
Thanks but can't declare @UID as NEWID() as this is generated for the record when the insert query is executed.
It has to be a return value after insert query execution.
The value then is used for another insert query for another table
0
Dorababu MSenior Software EngineerCommented:
You can use as said

SET @empId =  NEWID() 

SELECT @empId

Open in new window

0
RIASAuthor Commented:
Thanks,
Can you please provide a full code .
I am getting confused here.
0
Dorababu MSenior Software EngineerCommented:
DECLARE @MyTableVar table([UID] [uniqueidentifier]);
 INSERT [DIY] ([title], [name],[uid])
        OUTPUT INSERTED.[uid] INTO @MyTableVar
 VALUES ('''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''' ,	);

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Thanks will try and brb
0
RIASAuthor Commented:
Thanks.
0
Dorababu MSenior Software EngineerCommented:
Here is the complete script

DECLARE @MyTableVar table([UID] [uniqueidentifier]);
DECLARE @uid [uniqueidentifier]

INSERT into testbl ([title], [name],[uid])
        OUTPUT INSERTED.[uid] INTO @MyTableVar
 VALUES ('test','test1',NEWID());

 select @uid = uid from @MyTableVar

 SELECT @uid

Open in new window

0
RIASAuthor Commented:
Thanks Can you please show in this
Declare   @UID Nvarchar(MAX)=null

INSERT INTO DIY
		(
			[Title],
			[Name],
				
				
		) 
		VALUES
		(
			CAST(@Title AS  Nvarchar(10)),
			CAST(@Name AS NVARCHAR(50)),	
		   



  SET @UID  = @@IDENTITY



INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @UID+''' ,		
		
			
		)
    

Open in new window

0
Dorababu MSenior Software EngineerCommented:
DECLARE @guid uniqueidentifier

DECLARE @MyTableVar TABLE
(
  UID nvarchar(max)
)

INSERT into testbl (title, name,uid)
        OUTPUT INSERTED.uid INTO @MyTableVar
 VALUES ('test','test1',NEWID());

 select @guid = UID from @MyTableVar

INSERT INTO  TABLE_DEBUG(SQL) VALUES ( 'INSERT INTO DIY	
			[Title],
			[Name],
			[UID],		
			
				
			
		) 
		VALUES
		(
			'''+@Title + ''' ,		
			'''+@Name +''' ,
			'''+ @guid +''' ,		
		
			
		)

Open in new window

1
Dorababu MSenior Software EngineerCommented:
Replace with your actual table
0
RIASAuthor Commented:
Thanks, will try and be right back
0
RIASAuthor Commented:
Dorababu, Thanks a lot , will you please look at this question, I am stuck on it for ages


https://www.experts-exchange.com/questions/29075092/Change-query-with-an-additional-field.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.