TSQL Basics I: Stored Procedures In An Intrepreted Language

This series of posts will discuss some TSQL semantics and language features that may
confuse some users new to TSQL.  Specially those users familiar with other popular imperative programming languages and familiar with the SQL query language, but not familiar with TSQL.

In this post I consider some interesting semantics associated with TSQL stored procedures (SPs) and user defined functions (UDFs) , that arise becuase TSQL is intrepreted, that make TSQL SPs and UDFs very different from a procedure or function in other programming languages.  The difference is in how procedures and functions are linked together (or more accurately, not linked together) in TSQL. Because TSQL is an interpreted language, SPs are entities unto themselves imposing no dependencies on other SPs, UDFs or tables they reference (and thus no binding or linking occurs between a SP and any database objects it refers to).  TSQL will allow you to create a stored procedure that calls stored procedures that don't exist, and won't complain if you DROP or ALTER a stored procedure that is called in other stored procedures.  This is a little strange to someone used to compiled languages (and many other interpreted languages) where procedures and functions are linked together at compile time (or create time) and must always exist if they are being called.

This design effects which errors in a SP are caught when the SP is created, and which errors are caught when the SP is executed.  Ideally, SQLServer would catch all possible errors in a stored procedure when you create the procedure (just as when you compile a C\C++\C# procedure or function).  Simple errors such as syntax errors and type mismatches amongst variables are always found when you create a stored procedure. 

But, when you try to do something like:

CREATE PROC innerProc (@param1 INT) AS
BEGIN
    PRINT @param1;
END
go

CREATE PROC outerProc (@param1 INT) AS
BEGIN
    EXEC innerProc 'This is not an Integer' ;
END
go

No error will be generated when outerProc is created even though innerProc takes an integer parameter, not a string. This is becuase any errors related to the calling of innerProc by outerProc will not be caught until runtime (ie., innerProc may not exist, may not take an integer parameter, may have more then one parameter etc.).  This seems unintuitive to any developer familiar with strongly-typed compiled languages.  This behavior makes perfect sense when you consider that innerProc can be DROPed or ALTERed independent of where it is called (outerProc imposes no dependency on innerProc). Because innerProc can be DROPed or ALTERed without any effect on outerProc, we always need to do a runtime check anyways, so the create time check doesn't really buy us anything.

Also, consider that the following is legal TSQL:

CREATE PROC outerProc (@param int,@proc varchar(20)) AS
BEGIN
    EXEC @proc @param
END

In this case, there is nothing for us to check when outerProc is created. The same idea follows for other database objects referenced by stored procedures:

CREATE PROC thisIsAProc AS
BEGIN
 SELECT nonExistentColumn FROM nonExistentTable
END

The fact that we are selecting a nonExistentColumn from a nonExistentTable is not flagged as error because nonExistentTable may exist when the stored procedure is executed.

But, consider this stored procedure:

CREATE PROC thisIsAProc AS
BEGIN
    SELECT nonExistentColumn FROM ExistingTable
END

This on the other hand generates an error (nonExistentColumn is not a column of ExistingTable) as it represents a dependency that can be checked (and is checked).  This is a little inconsistent with the nested store procedure case, which is never checked during creation.

The bottom line:   You need to actually test and execute your stored procedures to
be sure anything that the stored procedure depends on (tables, UDFs, other stored procs) actually exists.  Just because you can create a stored procedure without generating an error doesn't mean it can ever (successfully) execute.