Dynamic SQL in stored procedure

By using sp_executesql (system stored procedure), you can write the dynamic SQL inside the stored procedure.

sp_ExecuteSql accepts a list of parameters, the first two of which MUST be either ntext, nchar or nvarchar. The first is your sql statement and the second is the parameter names. The third part is the list of parameter values.

Example:
DECLARE @SqlString nvarchar(4000)
SET @SqlString = N'SELECT * FROM ' + @TableName + ' WHERE ID = @ID';
EXECUTE sp_executesql
@SQLString
,N'@ID int'
,@ID

The @TableName and @ID are parameters passed into the stored procedure.

for more info, check:
http://doc.ddart.net/mssql/sql2000/html/acdata/ac_8_con_04_9uek.htm
http://msdn.microsoft.com/en-us/library/ms175170.aspx