Differences between SQL statements in stored procedure execution and in SSMs

Differences between SQL statements in stored procedure execution and in SSMs

SSMs is Sqlserever Management Studio. The scenario described in this article is tested in SQLServer2008.

Sometimes the same few statements are found, the OK is executed in SSMs, but the error is executed by the stored procedure. The problem basically involves the processing of temporary tables. Practice has found the following differences, programming developers must pay attention to, can be less detours.

1, after executing the stored procedure, the temporary table is no longer present and cannot be accessed. While SSMs executes, the intermediate temporary table can continue to be accessed as long as the current process is not closed (the current window).

2, in the stored procedure through the Exec execution statement, the dynamically generated temporary table, once the exec ends, even if the stored procedure is still executing, the subsequent statements can no longer access the temporary tables that were generated dynamically by the exec execution statement. If you want to continue to access, there are two ways, one is in the same exec procedure call, the second is to create a temporary table, insert the statement in Exec, then after the exec end, the temporary table will still exist to be accessible.

In general, the SSMs execution environment is more relaxed and generally performs normally, while stored procedures often produce errors. Therefore, the statement cannot be debugged only in SSMs, and must be called by the stored procedure to make a final conclusion.

Differences between SQL statements in stored procedure execution and in SSMs