Dynamic SQL Statements - 04 Nov 1999

One of my previous tips about executing stored procedures generated lots of mail about SQL statements. This week, I want to answer questions from those emails and share some tips from readers. I’ll continue this discussion for several weeks. One reader asked what I meant by a dynamic SQL statement. When I refer to a dynamic SQL statement, I mean a statement that is sent to SQL Server for processing. For instance, you can execute this statement in Query Analyzer:

select * from authors

Query Analyzer sends the entire statement to SQL Server, which executes it. This is known as a dynamic SQL statement because SQL Server must dynamically process the text of the statement at execution time. In contrast, you can execute a stored procedure named GetAuthors like this:

GetAuthors

This is not a dynamic statement because Query Analyzer sends the request to SQL Server, but SQL Server already processed the SQL in the stored procedure when the stored procedure was created. SQL Server needs only to complete the processing and execute the stored procedure’s code, which means SQL Server can execute the code much faster than it could if it were a dynamic query.

Another reader questioned my reference to using more than one SQL statement in a single statement. You can combine two or more SQL statements into one by placing them on the same line and separating them with a semi-colon:

select * from authors;  select * from titles;

You can type this statement in Query Analyzer and execute it. Query Analyzer will execute the code and return two recordsets. I added the space after the first semicolon for clarity; it doesn’t affect how the statements execute. When you combine statements that produce recordsets, you will generate more than one recordset. A group of statements like the one above is called a "batch." The batch of statements is sent to SQL Server as one group. SQL Server compiles the statements into one execution plan. Next week, I’ll continue to discuss methods for executing stored procedures and multiple statements.