Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Building Statements at Run Time

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Most Microsoft® SQL Server™ applications that have to dynamically build SQL statements at run time do so before calling a database API function or method to execute the statement. For example, a C-language application using ODBC can dynamically build one or more SQL statements into a character array, then pass that array to the ODBC SQLPrepare or SQLExecDirect functions.

Transact-SQL supports two methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

  • Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

  • Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string.

This is a simple example of using sp_executesql to execute a dynamically built string containing an SQL statement:

USE Northwind

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)

/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''

EXEC sp_executesql @SQLString

Security Note  Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft