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?