title: Writing stored procedures when using dynamic schema names in sql server
body: <p>The application, I have been currently working with has different schema names for its tables, for example Table1 can have multiple existence say A.Table1 and B.Table1. All my stored procedures are stored under dbo. I'm writing the below stored procedures using dynamic SQL. I'm currently using SQL Server 2008 R2 and soon it will be migrated to SQL Server 2012.</p>
  
  <pre><code>create procedure dbo.usp_GetDataFromTable1
  @schemaname varchar(100),
  @userid bigint
  as
  begin
      declare @sql nvarchar(4000)
      set @sql='select a.EmailID from '+@schemaname+'.Table1 a where a.ID=@user_id';
      exec sp_executesql @sql, N'@user_id bigint', @user_id=@userid
  end
  </code></pre>
  
  <p>Now my questions are,
  1. Is this type of approach affects the performance of my stored procedure?
  2. If performance is affected, then how to write procedures for this kind of scenario? </p>