How can I choose Union ALL according to passed parameter ( need optimization )?

Say we have a stored procedure that take @IsAllowed parameter which is boolean value and in case it passed True I开发者_运维问答 should select data from two tables A,B ( I'll use in my case Union ALL ) Else it passed False I should select data From one table A ... I write it int he following way :

Create PROCEDURE TestSP
(
    @IsAllowed bit
)
AS

IF @IsAllowed = 1
BEGIN
    Select ID, Username From A
    Union ALL
    Select ID, Username From B
END
ELSE
    Select ID, Username From A

Is it the best way to do that ? although in my real case SP have about 9 parameter used in where condition of First query, that's mean i'll write the first query twice and any change in it I must take care to have the same copy in the 2 different places

Try this out.

SELECT *
FROM   A
UNION ALL
SELECT *
FROM  B
WHERE @isAllowed = 1

I'm not really crazy about this query, but if you want to completely avoid the cost of the secondary select, I think you'll have to trade it off for a temp table.

IF OBJECT_ID('tempdb..#tableA') IS NOT NULL 
    DROP TABLE #tableA

SELECT  ID,Username
INTO    #tableA
FROM    tableA

IF @isAllowed = 1 
    BEGIN
        SELECT  ID,Username
        FROM    #tableA
        UNION ALL
        SELECT  ID,Username
        FROM    B
    END
ELSE 
    BEGIN
        SELECT  ID,Username
        FROM    #tableA
    END

DROP TABLE  #tableA

Feel free to change the # table to an @ table if your record set is small enough that you don't mind putting in memory (as opposed to writing it to the disk -- I/O is one of the primary bottlenecks). Just keep in mind you'll have to actually declare the table (with column definitions) as opposed to SELECTing INTO.