Writing stored procedures when using dynamic schema names in sql server

Question

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.

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

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?

Answer 1

The best way around this would be a redesign, if at all possible.

You can even implement this retrospectively by adding a new column to replace the schema, for example: Profile, then merge all tables from each schema into one in a single schema (e.g. dbo).

Then your procedure would appear as follows:

create procedure dbo.usp_GetDataFromTable1
@profile int,
@userid bigint
as
begin
    select a.EmailID from dbo.Table1 a 
    where a.ID = @user_id
    and a.Profile = @profile
end

I have used an int for the profile column, but if you use a varchar you could even keep your schema name for the profile value, if that helps to make things clearer.