Ad

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 INTO [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 INTO [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 getting this 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.

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

Stored procedure

Ad

Answer

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 [email protected] 

  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  [email protected]
Ad
source: stackoverflow.com
Ad