CODEWITHSUNDEEP
sqlstored-proceduresinsertnullsql-server-2014
aryan
aryan

Reputation: 49

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

Upvotes: 1

Views: 439

Answers (3)

Rokuto
Rokuto

Reputation: 814

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

Upvotes: 1

Gagan Sharma
Gagan Sharma

Reputation: 220

This error comes when you try to insert null data on the columns where null value is not allowed. In your query id can not be null when your inserting data in both tables. You can insert ID or make id as identity.

Upvotes: 0

Jens
Jens

Reputation: 69440

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

Returns null. remove the where clause

Upvotes: 0

Related Questions