aryan aryan - 1 month ago 7
SQL Question

Stored Procedure statement terminated Error?

I am using below-stored procedure to insert records from another view.

ALTER PROCEDURE [dbo].[SPemployeeinsert]
(
@date datetime
)
AS
BEGIN

DECLARE @idinsert as int
select @idinsert= MAX (ID)+1 from dbo.EMP


INSERT [SRV-RVS].dbo.emp (LASTNAME,ID)
SELECT [FirstName],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
FROM drv-rds2014. [HumanResources].[testEmployeeView]
WHERE ModifiedDate=@date

insert [SRV-RVS].dbo.empldf
(CIVILID,JOBTITLE,ISSUEDATE,ID)
select [PhoneNumber],[JobTitle],[ModifiedDate],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
FROM drv-rds2014. [HumanResources].[testEmployeeView]
WHERE ModifiedDate=@date
END


While executing the stored procedure I am returning the below error

Msg 515, Level 16, State 2, Procedure SPemployeeinsert, Line 42
Cannot insert the value NULL into column 'ID', table 'SRV-RVS.dbo.EMP'; column does not allow nulls. INSERT fails.


(1 row(s) affected)



Msg 515, Level 16, State 2, Procedure SPemployeeinsert, Line 48
Cannot insert the value NULL into column 'ID', table 'SRV-RVS.dbo.EMPLDF'; column does not allow nulls. INSERT fails.


I am trying to pass date like '01/04/2009;' which will copy from the source and insert into the destination using this stored procedure.

Regards

Screenshot
SToredProcedure

Answer Source

There are some problems with your procedure. First:

DECLARE @idinsert as int
select @idinsert= MAX (ID)+1 from dbo.EMP
where ID= @idinsert 

Variable @idinsert is not initialized, so its value is NULL. You need to change this to:

DECLARE @idinsert as int
select @idinsert= MAX(ID)+1 from dbo.EMP

Second problem - for @date it is possible, that your view drv-rds2014. [HumanResources].[testEmployeeView] will return multiple values and insert queries will fail cause of duplicate values in column Id. You need to change your insert statements to:

INSERT [SRV-RVS].dbo.emp (LASTNAME,ID)
  SELECT [FirstName],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
  FROM drv-rds2014. [HumanResources].[testEmployeeView]
  WHERE ModifiedDate=@date 

  insert [SRV-RVS].dbo.empldf
  (CIVILID,JOBTITLE,ISSUEDATE,ID)
  select [PhoneNumber],[JobTitle],[ModifiedDate],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
  FROM drv-rds2014. [HumanResources].[testEmployeeView]
  WHERE  ModifiedDate=@date