Using SQL Server 2019 (v15.0.2000.5) and SQL Server Management Studio 15.0.18390.0.

I have a stored procedure with a simple job: add values to the table.

CREATE DATABASE productsdb;
GO

CREATE TABLE Products 
(
    Id INT PRIMARY KEY IDENTITY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(30) NOT NULL,
    Price MONEY NOT NULL,
    InStockAmount INT DEFAULT 0 NOT NULL
);

CREATE PROCEDURE AddProduct
    @name NVARCHAR(30),
    @manufacturer NVARCHAR(30),
    @price MONEY,
    @amount INT
AS
    INSERT INTO Products(ProductName, Manufacturer, Price, InStockAmount)
    VALUES (@name, @manufacturer, @price, @amount)

Then I execute the procedure:

EXEC AddProduct 'Galaxy C7', 'Samsung', 22000, 4

And weird magic happens:

(1 row affected)

(1 row affected)

(1 row affected)

... [32 times in total]

Msg 217, Level 16, State 1, Procedure AddProduct, Line 25 [Batch Start Line 2]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

When doing SELECT * FROM Products;, there are really 32 copies of same product:

Also, I'd noticed that amount somehow goes from 4 to 5.

Any thought what could be the issue of unintended looping?

CodePudding user response：

I didn't separate the batches.

After coding the procedure I decided to test it by executing, but in the same batch. Which made procedure recursive (EXEC call was included to the procedure):

CREATE PROCEDURE AddProduct
    @name NVARCHAR(30),
    @manufacturer NVARCHAR(30),
    @price MONEY,
    @amount INT
AS
    INSERT INTO Products (ProductName, Manufacturer, Price, InStockAmount) 
    VALUES (@name, @manufacturer, @price, @amount);

-- Here I didn't separate the batches, which was a mistake

EXEC AddProduct 'Galaxy C7', 'Samsung', 22000, 4;

Adding a GO statement where comment is fixed the issue.