Getting a null in inserting data using scope_identity

Question

0.00/5 (No votes)

See more:

hello guys, is it possible to insert a data twice? in a single data stored procedure? i mean if i have employeeId, firstName and middleName to be inserted in employee table and the i have another statement in my stored procedure that i want to insert the same employee number in table of employeeImages, imagesId,employeeId,employeeImages, will it be okay? because im getting null but i can insert the image.

btw here's my code.

USE [northwind]
GO
/****** Object:  StoredProcedure [dbo].[spCreateAttrition]    Script Date: 01/21/2019 1:14:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spCreateAttrition]
	 @EmployeeID bigint,
	 @WaveNumber int,
	 @LastName varchar(50),
	 @FirstName varchar(50),
	 @MiddleName varchar(50),
	 @Address varchar(50),
	 @Region varchar(50),
	 @City varchar(50),
	 @ZipCode varchar(50),
	 @ContactNumber int,
	 @EmailAddress varchar(50),
	 @ContactPerson varchar(50),
	 @ContactPnumber int,
	 @ImmediateSupervisor varchar(50),
	 @HireDate date,
	 @StartDate date,
	 @RegularDate date,
	 @Status varchar(50),
	 @AttritionReason varchar(50),
	 @VoluntaryExpiration varchar(50),
	 @NonVoluntary varchar(50),
	 @Remarks varchar(50),
	 @EmployeePicture image
AS
BEGIN
	DECLARE @imageID int

	IF NOT EXISTS(SELECT * FROM employeeAttritions WHERE EmployeeID = @EmployeeID AND WaveNumber = @WaveNumber)
		BEGIN
			INSERT INTO employeeAttritions(EmployeeID, WaveNumber, LastName, FirstName, MiddleName, Address, Region, City, ZipCode,
						ContactNumber, EmailAddress,ContactPerson,ContactPnumber,ImmediateSupervisor,
						HireDate,StartDate,RegularDate,Status,AttritionReason,VoluntaryExpiration,NonVoluntary,Remarks)
				VALUES (@EmployeeID, @WaveNumber, @LastName, @FirstName,@MiddleName, @Address, @Region, @City,@ZipCode,
						@ContactNumber,@EmailAddress,@ContactPerson,@ContactPnumber,@ImmediateSupervisor,@HireDate,
						@StartDate,@RegularDate,@Status,@AttritionReason,@VoluntaryExpiration,@NonVoluntary,@Remarks)

			SET @EmployeeID = SCOPE_IDENTITY()
		 END
	ELSE
		RAISERROR('EmployeeID (%s) already exists', 16, 0, @EmployeeID)

	IF NOT EXISTS(SELECT * FROM attritionImages WHERE @imageID = imageID AND EmployeeID = @EmployeeID)
		BEGIN
			INSERT INTO attritionImages (EmployeeID, EmployeePicture)
				VALUES (@EmployeeID, @EmployeePicture)

			SET @imageID = SCOPE_IDENTITY()
		END
	ELSE
		RAISERROR('ImageID (%s) already exists', 16, 0, @imageID)
END

What I have tried:

im planning to change this into join statement but i still want to make this work.

Posted 20-Jan-19 18:42pm

Reden Rodriguez

Updated 20-Jan-19 20:25pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-01-20T20:25:00

Yes, you can. But ... not like that.
Your code is trying to insert the value to the DB:

SQL

INSERT INTO employeeAttritions(EmployeeID, WaveNumber, ...) VALUES (@EmployeeID, @WaveNumber, ...)

And then read the identity value from the system immediately after:

SQL

SET @EmployeeID = SCOPE_IDENTITY()

But if you can INSERT the value then it isn't an IDENTITY field, so there is no SCOPE_IDENTITY value to read back, so you get a NULL, unless there is a different field in the row that contains an IDENTITY value (But that implies that the EmployeeID value can be dupolicated, which would be a very odd thing to do).

You can't pass the EmployeeID from your presentation code and expect things to work: you should never try to pre-assign values as it will always give complicated and "random" errors in production which are very hard to track down, but which completely mess up your database.

Stop and think about what you are trying to do!