Solved

Using SCOPE_IDENTITY()

Posted on 2006-07-13
2
796 Views
Last Modified: 2008-02-01
I've got an sp which inserts a record into a table. It does this by building the Sql Statement dynamically and using Exec sp_ExecuteSQL

So my sp looks similar in structure to this:

select @SqlStatment = 'Insert into Table1 (Field2, Field3, Field4) Values(23,98,1212)'
Exec sp_ExecuteSQL @SqlStatement

The table has an identity column for the PrimaryKey. I need to get this value back to use as a foreign key for other Insert statements. So how would I modify the above example to incorporate Select SCOPE_IDENTITY() into it?

Ian
0
Comment
Question by:ipendlebury
2 Comments
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 250 total points
ID: 17100032
you do not need to use scope_identity
use Ident_Current

ie
declare
@newId int
select @SqlStatment = 'Insert into Table1 (Field2, Field3, Field4) Values(23,98,1212)'
Exec sp_ExecuteSQL @SqlStatement

select @newID = Ident_Current('table1')

/Richard
0
 

Author Comment

by:ipendlebury
ID: 17100088
I didn't even know of the existance of Ident_Current(). It works a treat. Thank you.

Ian
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now