title: Stored procedure with table name as parameter amongst others
body: <p>I have tried to update a stored procedure which worked fine without the need to use sp_executesql. I now want to have the table name as a parameter as I have a number of tables with the same structure and don't want to create new stored procedures for each of them. </p>
  
  <p>The problem I have is that this version seems to require all the parameters, while the previous one accepted any number of parameters. For instance, if I remove all the WHERE parameters and just have the @TableName parameter it works fine. I;ve tried looking for an example, but I cannot find anything like this. All the examples of parsing the table name have only that parameter.</p>
  
  <pre><code>CREATE PROCEDURE cafgTenantNamesTEST2
      @TableName sysname,
      @Square nvarchar(100) = null,
      @Location nvarchar(100) = null,
      @Name nvarchar(100) = null,
      @NormalizedName nvarchar(100) = null,
      @SharedLand int = 0,
      @FieldNumber int = 0,
      @Description nvarchar(255) = null,
      @Dwelling nvarchar(100) = null
  AS
  BEGIN
      DECLARE @sql AS NVARCHAR(MAX)
      SET @sql = 'SELECT * FROM [' + @TableName + ']' + 
      'WHERE ([Square] LIKE ''' + @Square + ''' OR ''' + @Square + ''' IS NULL)' + 
      'AND ([Location] = ''' + @Location + ''' OR ''' + @Location + ''' IS NULL)' +
      ...
      ...
  --PRINT @sql
  EXEC sp_executesql @sql
  END
  </code></pre>
  
  <p>Suggestions please.</p>