I have a table into which I have to insert data if a particular key is not present. I decided to write a stored procedure to insert data. What I want to do is:
- Create a stored procedure.
- Insert a bunch of rows using the stored procedure.
- Drop the stored procedure.
I want to do all this in a single script file so that no one ever knows that the stored procedure existed.
Here is what I have:
Create
CREATE PROCEDURE My_Proc(@key varchar(10))
AS
BEGIN
IF NOT EXISTS (SELECT * FROM [dbo].[My_Table] WHERE [key] = @key)
INSERT INTO [dbo].[My_Table] (key, created, modified)
VALUES (@key, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
END
Insert
EXECUTE My_Proc 99241
GO
EXECUTE My_Proc 99242
GO
EXECUTE My_Proc 99243
GO
Drop
DROP PROCEDURE My_Proc
Now I right click on my database in SQL Server Management Studio, click on New Query and paste the above query. I get the following output:
(1 row(s) affected)
(1 row(s) affected)
Msg 217, Level 16, State 1, Procedure My_Proc, Line 9
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
(1 row(s) affected)
Msg 217, Level 16, State 1, Procedure My_Proc, Line 9
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I am unable to figure out what is causing the error. Also, When I highlight the specific EXECUTE command on SSMS like so EXECUTE My_Proc 99241 the data gets inserted without any error.
1
You need a GO after your Create procedure. I believe the first “EXECUTE My_Proc 99241” is getting included as part of the procedure because of the missing GO, so basically you have a recursive call.
Create Procedure statement (as well as functions and similar statements) takes everything to the end of the batch, which is why they have to be followed by “GO” or end of the file.
0
If you have a list of keys, some of which might be in the table, then try something like:
insert into my_tableC(key, created, modified)
select key, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
from <list of keys query> k
where k.key not in (select key from my_table)
If you have the list, but not in a table, you can create it as:
with toinsert as (
select <keyval1> as key union all
select <keyval2> union all
select <keyval3> . . .
select <keyvaln>
)
insert into my_tableC(key, created, modified)
select key, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
from toinsert k
where k.key not in (select key from my_table)
2
Do you have a trigger on the table you are inserting into? Because it seems that there is some sort of looping going on with the table you are inserting into. the name of the Function/Procedure that is breaking is not the name of the procedure you created. notice the error message:
(1 row(s) affected) (1 row(s) affected) Msg 217, Level 16, State 1, Procedure InsertProfessionalComponentLookupForCpt, Line 9 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). (1 row(s) affected) Msg 217, Level 16, State 1, Procedure InsertProfessionalComponentLookupForCpt, Line 9 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Make sure there are not any triggers or Computed columns that are doing some extra work on this table.
1