how do I use the Identity_Scope as a value to insert in table in SQL Server

Question

Ive looked over and over again for help but nothing has helped, I have a stored procedure where i want to insert the last identity ie SCOPE_IDENTITY() as B but it keeps returning a NULL value

CREATE PROCEDURE dbo.Createletter

@A INT,
@B NVARCHAR(50),
@C NVARCHAR(50),


AS

BEGIN;
    SET NOCOUNT ON;

    BEGIN;

    INSERT INTO dbo.Employees ( A, B, C )
    VALUES                     ( @A, SCOPE_IDENTITY(),@C);
    END;

END;
GO

What is "the last identity"? You mean the highest ID in the table? In any case, SCOPE_IDENTITY() (not identity_scope() as you have here) returns the last identity from the most recent insert in the statement. Since you have no inserts before this one, there is no identity in the scope. Is there another procedure that runs the insert for FacultyEmployeeID? Sounds like you need to return that value from *that* insert and use it in *this* insert statement. And if it's another sproc you won't be able to use SCOPE_IDENTITY() as it would be another scope...I think you need to clarify a bit. — Jacob H, Dec 20 '17 at 16:48
ive tried Scope_Identity() in the insert statement of my procedure but still comes up NULL. also there are no procedures prior to the insert shown — Derek, Dec 20 '17 at 16:52
Then that is your exact problem. As I said in the previous comment, SCOPE_IDENTITY() returns the most recent ID value in the scope. Since you have no other inserts in this scope, you have no Identity value to return. Hence `NULL`. — Jacob H, Dec 20 '17 at 16:53
Again you will need to clarify what you mean by "the last identity". Sounds like you want SQL to generate a new ID but that is very unclear right now. — Jacob H, Dec 20 '17 at 16:56
if i have a principal thats the 7th record in a table...i need to store that in Scope_Identity() and use it later in an insert — Derek, Dec 20 '17 at 17:00
Could you provide your table structure? If you do not already, you may want to have a table that indicates whether or not the faculty member is a principal. This would make it easier to get the correct value. — user3183411, Dec 20 '17 at 17:02
How would SQL know that the principal is the 7th record in the table? I hope you don't want the computer to guess? Sounds like you need a SELECT statement with a WHERE clause to match the principal value and return the ID, then use that value in your INSERT here. — Jacob H, Dec 20 '17 at 17:03
It sounds like this table needs to contain foreign key values. You can also have SQL generate an ID automatically for each record that is inserted as mentioned in earlier comments. — user3183411, Dec 20 '17 at 17:06
We've all been there. Sounds like you may need to have a understanding on how relational databases work. This will give you an idea on how to create and link tables in your SQL server. Not sure what platform you are using but there are some helpful videos on youtube. check this guy out. He has some good stuff. https://www.youtube.com/watch?v=ZNObiptSMSI&list=PL08903FB7ACA1C2FB — user3183411, Dec 20 '17 at 17:10
Tag properly. Which dbms are you using? That's one of the most important information. Tell what you are trying to do. If you don't us what you want, how do you expect us to help you? Columns A, B, C won't help you much in getting answer, as you can see from people's comment. Here's a guide how to ask question. https://stackoverflow.com/help/how-to-ask — Eric, Dec 20 '17 at 17:31

score 0 · Answer 1 · answered Dec 20 '17 at 19:22

If you have an identity column in your database table, when you add new row to your table, the identity value is generated automatically. It is possible to fetch the created value for the last Insert statement as follows.

create table dbo.Employees (
    A int identity(1,1), 
    B nvarchar(50), 
    C nvarchar(50)
)

declare @B nvarchar(50), @C nvarchar(50)
INSERT INTO dbo.Employees ( B, C ) VALUES ( @B, @C);

select SCOPE_IDENTITY()

If you procedure is creating a record in the table, you might be trying to get the IDENTITY for that row, I guess

how do I use the Identity_Scope as a value to insert in table in SQL Server

1 Answers1