title: SQL Server 2008 - Cache Dynamic SQL
body: <p>I have Created a Procedure for one of our .Net Devs where they can pass in values that will then build and output a result set.  Part of the params they pass in will determine which table gets called.  My question is how can I ensure that the dynamic sql statement I am building will get cached for faster execution? None of the cache examples I read discussed dynamic table names (might have just overlooked), just dynamic params within the query.</p>
  
  <p>I am using sp_executesql and passing in params, but I am not sure how I use that method for deriving the table name without appending the Parameter to the select statement?</p>
  
  <p>Here is a simple example of how I am currently building the string to generate my result set.</p>
  
  <pre><code>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DynamicSQL]') AND type in (N'U'))
  DROP TABLE [dbo].[DynamicSQL]
  GO
  
  CREATE TABLE [dbo].[DynamicSQL](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [TestName] [varchar](100) NULL
  ) ON [PRIMARY]
  
  GO
  
  Insert Into DynamicSQL
  Values('Name1');
  Insert Into DynamicSQL
  Values('Name2');
  Insert Into DynamicSQL
  Values('Name3');
  Insert Into DynamicSQL
  Values('Name4');
  
  GO
  
  DECLARE @TableName VARCHAR(50),
          @SQL       NVARCHAR(500)
  
  SELECT @TableName = 'DynamicSQL',
         @SQL = 'Select *
              From ' + @TableName;
  
  execute sp_executesql @SQL;
  </code></pre>
  
  <p>Any info is greatly appreciated.</p>
  
  <p>--S   </p>