Sql-server – Reusing query for duplicate tables

sql server

I have Database A & Database B. Both have a User table.

I need to write one stored procedure that will retrieve the data from the correct table depending on a @Parameter passed in.

The idea of the query (data needing returned) in the stored proc needed:

SELECT * FROM dbA.User || OR || SELECT * FROM dbB.User

I have found the following ways to accomplish this but I want to minimize duplicate code nor add performance slowness/overhead.

Possibilities on my list:

Dynamic SQL – harder to maintain, adds overhead in that my
understanding cannot define execution plans
Create a duplicate proc or If/Else inside proc – duplicate code
never good
Table value function – not sure of a way to do this using it as I
can't use dynamic SQL here or If/Else to tell which query to run.

At this point I am leaning towards Option #2, duplicate code, with the understanding the database setup needs to be better if we want to avoid duplicating code.

I am not saying this is a good database setup, but it is what I am faced with. I have no control over combining database A & B or changing the structure to only have one User table.

Is there any other way to take care of this issue? Or thoughts on my approach?

Best Answer

Let's assume the value of @Parameter is dbA or dbB

SELECT * FROM dbA.User where @Parameter = 'dbA'
UNION ALL
SELECT * FROM dbB.User where @Parameter = 'dbB'

Related Solutions

Sql-server – Suddenly Slow Execution Plan for Stored Proc

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.

Sql-server – Error handling in container procedures

I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.

I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple THROW; (no other arguments) to re-raise the original exception.

Related Question