SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can you help me please with following issue!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/29/2013 :  19:25:46  Show Profile  Reply with Quote
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;

Edited by - DeniSys on 10/29/2013 19:36:58

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  01:21:51  Show Profile  Reply with Quote
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

Edited by - visakh16 on 10/30/2013 01:23:06
Go to Top of Page

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/30/2013 :  03:42:57  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  03:48:20  Show Profile  Reply with Quote
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
Go to Top of Page

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/30/2013 :  04:04:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  04:16:45  Show Profile  Reply with Quote
show the code used and how you called it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/30/2013 :  04:24:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  08:10:44  Show Profile  Reply with Quote
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
Go to Top of Page

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/30/2013 :  08:47:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  08:55:47  Show Profile  Reply with Quote
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
Go to Top of Page

DeniSys
Starting Member

Israel
6 Posts

Posted - 10/30/2013 :  09:05:32  Show Profile  Reply with Quote
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!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  12:06:16  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000