Building Dynamic SQL In a Stored Procedure – CodeProject

Introduction

A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set of statements stored in a variable and executed using a SQL command. There may be several methods of implementing this in SQL Server. This article will show you a good method of doing this. Before getting into a detailed explanation, let me tell “When to Use Dynamic SQL?" We can’t definitely say that a Static SQL will meet all our programming needs. A Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters. Say for example – An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE clause.

NOTE: Most importantly, the Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed.