CODEWITHSUNDEEP
sqlsql-server
user5515700
user5515700

Reputation:

SQL Server : procedure tables cannot insert value null

I want to add 100 storages in a table.

This is my procedure:

CREATE PROCEDURE [add100*sTORAGE] 
AS
    DECLARE @i int, @start DATETIME, @end DATETIME

    SET @start = GETDATE()
    SET @i = 1

    WHILE @i < 101
    BEGIN
        INSERT INTO Storage(storage_name) 
        VALUES (CONCAT('Nume', CONVERT(nvarchar, @i)))

        SET @i = @i +1
    END

    SET @end = GETDATE()

    DECLARE @testID INT = (SELECT TOP 1 (TestRunID) 
                           FROM TestRuns 
                           ORDER BY TestRunID DESC)

    DECLARE @tableID INT = (SELECT tableID 
                            FROM Tables 
                            WHERE Name = 'Storage')

    INSERT INTO TestRunTables (TestRunID, TableID, StartAt, EndAt) 
    VALUES (@testID, @tableID, @start, @end)
GO

I get an error after its execution:

Msg 515, Level 16, State 2, Procedure add100*sTORAGE, Line 13
Cannot insert the value NULL into column 'TestRunID', table 'OnlineShop.dbo.TestRunTables'; column does not allow nulls. INSERT fails.

When I look in the table, it has been created 99 columns.

I have some empty tables in a relation and this are the inserts of it (maybe here is the cause):

--INSERTS--

-- insert views into "Views"
INSERT INTO Views(Name) VALUES ('View1')
INSERT INTO Views(Name) VALUES ('View2')
INSERT INTO Views(Name) VALUES ('View3')

select * from views

delete from views where ViewID>1

-- insert into "Tests"
INSERT INTO Tests(Name) VALUES ('[add100*Storage-runView1-del100*Storage]')
INSERT INTO Tests(Name) VALUES ('[add100*Product-runView2-del100*Product]')
INSERT INTO Tests(Name) VALUES ('[add100*OrderProduct-runView3-    del100*OrderProduct]')

SELECT * FROM Tests

--insert into tables
INSERT INTO Tables(Name) VALUES ('Table1') 
INSERT INTO Tables(Name) VALUES ('Table2')
INSERT INTO Tables(Name) VALUES ('Table3')

SELECT * from Tables

-- insert into "testTable"
INSERT INTO TestTables(TestID, TableID, NoOfRows, Position) VALUES   (1,1,100,1)
INSERT INTO TestTables(TestID, TableID, NoOfRows, Position) VALUES (3,2,100,1)
INSERT INTO TestTables(TestID, TableID, NoOfRows, Position) VALUES (2,3,100,1)

SELECT * FROM TestTables

-- insert into "testViews"
INSERT INTO TestViews(TestID,ViewID) VALUES (1,1)
INSERT INTO TestViews(TestID,ViewID) VALUES (3,2)
INSERT INTO TestViews(TestID,ViewID) VALUES (2,3)

SELECT * FROM TestViews

What's wrong? Thank you.

Upvotes: 0

Views: 72

Answers (1)

Russ
Russ

Reputation: 4163

The error tells you everything--table TestRunTables has column "TestRunID" which requires that field to have a value. You either need to be sure to insert a value into that field, or alter the column so that it will use a default value when you don't specify it.

This line:

DECLARE @testID INT = (SELECT TOP 1 (TestRunID) FROM TestRuns ORDER BY TestRunID DESC)

will set @testID to null if no records are returned from TestRuns or if the first TestRunID is null. This is probably what you need to fix.

Upvotes: 2

Related Questions