Can there be local temporary tables with the same name in nested stored procedures, outer stored procedures and inner stored procedures of SQL Server? If so, are there any problems or restrictions? In a nested stored procedure, do you call the outer stored procedure’s temporary table or the self-defined temporary table? Is the local temporary table similar to the high-level language variable“Scope“What about the scope?
Note: it can also be called parent stored procedure and child stored procedure, outer stored procedure and inner stored procedure. These are just different names. We use outer stored procedure and inner stored procedure uniformly here. The following part of the article will not be described.
Let’s start with an example. As shown below, we construct aA simple example.
The simple test seems to be normal and nothing wrong. If you come to a conclusion at this time, it’s too early! For example, if you see a swan is white, if you come to a conclusion:“All swans are white”In fact, there are black swans in the world, but you haven’t seen them! Let’s modify the stored procedure as shown belowdbo.PRC_SUB_TEST, using the field namenamereplace*, as follows:
Then repeat the above test, as shown below, and execute the stored proceduredbo.PRC_TESTIf so, an error will be reported:“Invalid column name ‘name’.”
At this point, I only need to execute the stored procedure oncedbo.PRC_SUB_TESTAnd then execute the stored proceduredbo.PRC_TESTYou won’t make a mistake. And the stored procedure is executed only once, and then in the current session or any other sessiondbo.PRC_TESTNo mistake. Is it very confusing or confusing。
If you want to reproduce this phenomenon again, you can only do it through the following stepsSQLOr delete/This phenomenon can only be reproduced by reconstructing the stored procedure. It’s kind of ghostly.
This phenomenon is described in the official document (see the link to resources for details)
A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. This is shown in the following example.
A local temporary table created in a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger was called. However, if the query references a temporary tableIf there are two temporary tables with the same name at the same time, it does not define which table to resolve the query. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the calling stored procedure. However, in order to modify it to resolve to a table created during nesting,This table must have the same structure and column name as the table created by the calling procedure. The following example illustrates this.
In official documents“If there are two temporary tables with the same name at the same time, it does not define which table to resolve the query”This kind of explanation is still a bit confusing. Here is a brief explanation. In the nested call of stored procedures, local temporary tables with the same name are allowed in the outer and inner stored procedures. However, if you want to modify or parse them in the memory process (modification is easy to understand, such as adding index, adding field and other DDL operations; about parsing, querying temporary tables, specifying field names in SQL, you need to To analyzeresolve）In this case, the temporary table must have the same table structure,Otherwise, it will report an error. The official document tells you that you can’t, but it doesn’t tell you why. So we might as well make some speculation about whether two local temporary tables are created in the nested calls of stored procedures? Is it possible to actually create only one local temporary table? What about local temporary table reuse? Let’s verify it briefly. As shown below, we can judge that two local temporary tables are actually created. There is no temporary table reuse.
Of course, you can use the following SQL to verify, which is consistent with the results of the above verification.
Then let’s take a look at the temporary table“Scope”I’m sorry to use such a concept. The official documents don’t have such a concept. It’s just one aspect of our thinking. There’s no need to argue about details. Let’s modify the stored procedure as shown below
Through experimental verification, we find that the temporary table of the outer stored procedure is effective in the inner stored procedure“Scope”Before the creation of a temporary table with the same name of an inner stored procedure, this is similar to the scope of global variables and local variables in high-level languages.
Since two local temporary tables have been created, why do you report errors when modifying or parsing them? One of my personal guesses is that after the optimizer parses, in the process of execution, when parsing or modifying, the database engine cannot judge or there is no such logic in the code to control which temporary table to retrieve. It may be a defect in the code or some logical reason. The above is just a personal guess and reasoning. If there is any deficiency or mistake, please correct.