Can you help me please with following issue!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can you help me please with following issue!

Author

Topic

DeniSys
Starting Member

6 Posts

Posted - 2013-10-29 : 19:25:46

Hi All,
I'm trying to create two tables Employees and Addresses and store procedure to add new employee with specify data in two tables.
Important to me, to create ID for table Addresses by specify condition. Created AddrID must be the same in both tables.

Thanks a lot.

The Error message is:

Msg 515, Level 16, State 2, Procedure sp_AddNewEmployee, Line 28
Cannot insert the value NULL into column 'AddrID', table 'NewDB291013.dbo.Employees'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_AddNewEmployee, Line 31
Cannot insert the value NULL into column 'AddrID', table 'NewDB291013.dbo.Addresses'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Please help me to find solution.

Here is the source code:

USE NewDB291013;
GO

--------------------------------------------
--TABLE EMLOYEES
--------------------------------------------

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;
GO

CREATE TABLE dbo.Employees
(
EmpID INT NOT NULL IDENTITY(1000, 1),
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(30) NOT NULL,
AddrID NVARCHAR(50) NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY(EmpID)
CONSTRAINT FK_Employees_Addresses FOREIGN KEY(AddrID)
REFERENCES dbo.Addresses(AddrID)
);
GO

--------------------------------------------
--TABLE ADDRESSES
--------------------------------------------

IF OBJECT_ID('dbo.Addresses', 'U') IS NOT NULL DROP TABLE dbo.Addresses;
GO

CREATE TABLE dbo.Addresses
(
AddrID NVARCHAR(50) NOT NULL,
Addr NVARCHAR(50) NOT NULL,
City NVARCHAR(50) NOT NULL,
Region NVARCHAR(50) NULL,
Country NVARCHAR(50) NOT NULL,
PostIndex NVARCHAR(50) NOT NULL,
Email NVARCHAR(50) NULL,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
Mobile NVARCHAR(50) NULL
CONSTRAINT PK_Addresses PRIMARY KEY(AddrID)
);
GO

-----------------------------------------------------
--ADD EMPLOYEE STORE PROCEDURE AND ADDRID GENERATION
-----------------------------------------------------

IF OBJECT_ID('dbo.sp_AddNewEmployee', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_AddNewEmployee;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.sp_AddNewEmployee
(
@FirstName NVARCHAR(30),
@LastName NVARCHAR(30),
@Addr NVARCHAR(50),
@City NVARCHAR(50),
@Region NVARCHAR(50),
@Country NVARCHAR(50),
@PostIndex NVARCHAR(50),
@Email NVARCHAR(50),
@Phone NVARCHAR(50),
@Fax NVARCHAR(50),
@Mobile NVARCHAR(50)
)

AS

SET NOCOUNT ON;

BEGIN

DECLARE @EmpID INT
DECLARE @AddrID NVARCHAR(50)
SELECT @EmpID = @@IDENTITY
SELECT @AddrID = N'ADDR' + CAST(@EmpID AS NVARCHAR) + CAST(CAST(ASCII(RIGHT(@FirstName, 3)) AS INT) AS NVARCHAR)

INSERT INTO dbo.Employees(FirstName, LastName, AddrID)
VALUES(@FirstName, @LastName, @AddrID)

INSERT INTO dbo.Addresses(AddrID, Addr, City, Region, Country, PostIndex, Email, Phone, Fax, Mobile)
VALUES(@AddrID, @Addr, @City, @Region, @Country, @PostIndex, @Email, @Phone, @Fax, @Mobile)

END
GO

--------------------------------------------
--STORE PROCEDURE EXECUTION
--------------------------------------------

EXEC dbo.sp_AddNewEmployee

@FirstName = 'DeniSys',
@LastName = 'DeniSys',
@Addr = 'abcdefg 12345',
@City = 'AbcdefG',
@Region = 'abcd',
@Country = 'ABC',
@PostIndex = '49123',
@Email = 'abcdefg@abc.com',
@Phone = NULL,
@Fax = NULL,
@Mobile = '123-123456789';
GO

--------------------------------------------
--CHECK INSERT QUERY
--------------------------------------------

SELECT * FROM dbo.Employees AS E
JOIN dbo.Addresses AS A
ON A.AddrID = E.AddrID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 01:21:51

your procedure statements are not in correct order.
it should be like below
Also use SCOPE_IDENTITY rather than @@IDENTITY as former will give you id generated in correct scope


----------------------------------------------------

IF OBJECT_ID('dbo.sp_AddNewEmployee', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_AddNewEmployee;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.sp_AddNewEmployee
(
@FirstName	 NVARCHAR(30),
@LastName	 NVARCHAR(30),
@Addr	 NVARCHAR(50),
@City	 NVARCHAR(50),
@Region	 NVARCHAR(50),
@Country	 NVARCHAR(50),
@PostIndex	 NVARCHAR(50),
@Email	 NVARCHAR(50),
@Phone	 NVARCHAR(50),
@Fax	 NVARCHAR(50),
@Mobile	 NVARCHAR(50)
)

AS

SET NOCOUNT ON;

BEGIN

DECLARE @AddrID	 NVARCHAR(50)
DECLARE @EmpID	 INT

INSERT INTO dbo.Employees(FirstName, LastName, AddrID)
VALUES(@FirstName, @LastName, @AddrID)


SELECT @EmpID = @@IDENTITY SCOPE_IDENTITY()
SELECT @AddrID = N'ADDR' + CAST(@EmpID AS NVARCHAR) + CAST(CAST(ASCII(RIGHT(@FirstName, 3)) AS INT) AS NVARCHAR)


INSERT INTO dbo.Addresses(AddrID, Addr, City, Region, Country, PostIndex, Email, Phone, Fax, Mobile)
VALUES(@AddrID, @Addr, @City, @Region, @Country, @PostIndex, @Email, @Phone, @Fax, @Mobile)

END
GO

then call it using your statement


--------------------------------------------
--STORE PROCEDURE EXECUTION
--------------------------------------------

EXEC dbo.sp_AddNewEmployee

@FirstName	 =	 'DeniSys',
@LastName	 =	 'DeniSys',
@Addr	 =	 'abcdefg 12345',
@City	 =	 'AbcdefG',
@Region	 =	 'abcd',
@Country	 =	 'ABC',
@PostIndex	 =	 '49123',
@Email	 =	 'abcdefg@abc.com',	
@Phone	 =	 NULL,
@Fax	 =	 NULL,
@Mobile	 =	 '123-123456789';
GO

--------------------------------------------
--CHECK INSERT QUERY
--------------------------------------------

SELECT * FROM dbo.Employees AS E
JOIN dbo.Addresses AS A
ON A.AddrID = E.AddrID;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeniSys
Starting Member

6 Posts

Posted - 2013-10-30 : 03:42:57

quote:

Originally posted by visakh16

your procedure statements are not in correct order.
it should be like below
Also use SCOPE_IDENTITY rather than @@IDENTITY as former will give you id generated in correct scope


----------------------------------------------------

IF OBJECT_ID('dbo.sp_AddNewEmployee', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_AddNewEmployee;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.sp_AddNewEmployee
(
@FirstName	 NVARCHAR(30),
@LastName	 NVARCHAR(30),
@Addr	 NVARCHAR(50),
@City	 NVARCHAR(50),
@Region	 NVARCHAR(50),
@Country	 NVARCHAR(50),
@PostIndex	 NVARCHAR(50),
@Email	 NVARCHAR(50),
@Phone	 NVARCHAR(50),
@Fax	 NVARCHAR(50),
@Mobile	 NVARCHAR(50)
)

AS

SET NOCOUNT ON;

BEGIN

DECLARE @AddrID	 NVARCHAR(50)
DECLARE @EmpID	 INT

INSERT INTO dbo.Employees(FirstName, LastName, AddrID)
VALUES(@FirstName, @LastName, @AddrID)


SELECT @EmpID = @@IDENTITY SCOPE_IDENTITY()
SELECT @AddrID = N'ADDR' + CAST(@EmpID AS NVARCHAR) + CAST(CAST(ASCII(RIGHT(@FirstName, 3)) AS INT) AS NVARCHAR)


INSERT INTO dbo.Addresses(AddrID, Addr, City, Region, Country, PostIndex, Email, Phone, Fax, Mobile)
VALUES(@AddrID, @Addr, @City, @Region, @Country, @PostIndex, @Email, @Phone, @Fax, @Mobile)

END
GO

then call it using your statement


--------------------------------------------
--STORE PROCEDURE EXECUTION
--------------------------------------------

EXEC dbo.sp_AddNewEmployee

@FirstName	 =	 'DeniSys',
@LastName	 =	 'DeniSys',
@Addr	 =	 'abcdefg 12345',
@City	 =	 'AbcdefG',
@Region	 =	 'abcd',
@Country	 =	 'ABC',
@PostIndex	 =	 '49123',
@Email	 =	 'abcdefg@abc.com',	
@Phone	 =	 NULL,
@Fax	 =	 NULL,
@Mobile	 =	 '123-123456789';
GO

--------------------------------------------
--CHECK INSERT QUERY
--------------------------------------------

SELECT * FROM dbo.Employees AS E
JOIN dbo.Addresses AS A
ON A.AddrID = E.AddrID;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thank you for quick respond.

i tried but without success.
Can you please show me exactly what to do in existing source code?

Thank you again.
Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 03:48:20

What does that mean? I change your existing code only

Why cant you just copy and paste it and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeniSys
Starting Member

6 Posts

Posted - 2013-10-30 : 04:04:55

quote:
Originally posted by visakh16

What does that mean? I change your existing code only

Why cant you just copy and paste it and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

oh, sorry
i did it but i received the same error again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 04:16:45

show the code used and how you called it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeniSys
Starting Member

6 Posts

Posted - 2013-10-30 : 04:24:43

quote:
Originally posted by visakh16

show the code used and how you called it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I'm just replaced old SP by new and re-create it.
then i running

EXEC dbo.sp_AddNewEmployee

@FirstName = 'DeniSys',
@LastName = 'DeniSys',
@Addr = 'abcdefg 12345',
@City = 'AbcdefG',
@Region = 'abcd',
@Country = 'ABC',
@PostIndex = '49123',
@Email = 'abcdefg@abc.com',
@Phone = NULL,
@Fax = NULL,
@Mobile = '123-123456789';
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:10:44

ok i got the issue

The current way its setup it wont work
Reason is you've cyclic reference ie Employee table has AddrID field as NOT NULL and for getting the value in AddrID you would require generated EmpID value ie insertion in Employee to be completed.
The solution is to make AddrID NULL able in EMployee table, fiorst do insert to employee to get the generated EmpID, then you can use it to generate value for @AddrID and do insertion to Address table and also update AddrID field in Employee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeniSys
Starting Member

6 Posts

Posted - 2013-10-30 : 08:47:23

quote:
Originally posted by visakh16

ok i got the issue

The current way its setup it wont work
Reason is you've cyclic reference ie Employee table has AddrID field as NOT NULL and for getting the value in AddrID you would require generated EmpID value ie insertion in Employee to be completed.
The solution is to make AddrID NULL able in EMployee table, fiorst do insert to employee to get the generated EmpID, then you can use it to generate value for @AddrID and do insertion to Address table and also update AddrID field in Employee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

i understand, thank you. But update AddrID in Employees i can in SP. Can you send me the source code with your resolution?
just copy past with your update please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:55:47

yep

just do like


UPDATE dbo.Employees
SET AddrdID = @AddrID
WHERE EmpID = @EmpID

this has to come after the @AddrID variable value assignment step.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeniSys
Starting Member

6 Posts

Posted - 2013-10-30 : 09:05:32

quote:
Originally posted by DeniSys

quote:
Originally posted by visakh16

ok i got the issue

The current way its setup it wont work
Reason is you've cyclic reference ie Employee table has AddrID field as NOT NULL and for getting the value in AddrID you would require generated EmpID value ie insertion in Employee to be completed.
The solution is to make AddrID NULL able in EMployee table, fiorst do insert to employee to get the generated EmpID, then you can use it to generate value for @AddrID and do insertion to Address table and also update AddrID field in Employee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

i understand, thank you. But update AddrID in Employees i can in SP. Can you send me the source code with your resolution?
just copy past with your update please

Yes!!! IT"S WORKING!!! YOU ARE THE GREAT MAN!!!
THANK YOU VERY VERY MUCH!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 12:06:16

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources