Inlining simple system procedures

A system procedure defined only as a single SELECT statement in the body is sometimes inlined when called in the FROM clause of a query. That is, the query is rewritten to be equivalent to the original query but without the procedure definition. When a procedure is inlined, it is rewritten as a derived table. A procedure is never inlined if it uses default arguments, or if it contains anything other than a single SELECT statement in the body.

For example, suppose you create the following procedure:

CREATE PROCEDURE Test1( arg1 INT )
 BEGIN
  SELECT * FROM Employees WHERE EmployeeID=arg1
 END;

Now suppose you call the procedure in a FROM clause of a query as follows:

SELECT * FROM Test1( 200 );

The database server may rewrite the query as follows:

SELECT * FROM ( SELECT * FROM Employees WHERE EmployeeID=CAST( 200 AS INT ) ) AS Test1;

Inlining simple system procedures

See also