Post reply

Variable name in FROM clause

  • February 20, 2003 at 9:06 am

    #81127

    Is it possible to have a variable name in the FROM clause of a SELECT statement.

    For example I want to write a stored procedure that will select the same columns from two tables (current and history), instead of writting the same stored procedure twice I would like to have a variable in the FROM and pass the parameter when it is called.

    Thank you

    Stéphane

  • February 20, 2003 at 10:43 am

    #449402

    you could always use an exec('select <whatever> from ' + @tableName), but I'd not be surprised to hear there are performance issues with this strategy, right guys?

  • February 20, 2003 at 12:29 pm

    #449411

    Is there any relation between the to tables?

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • February 20, 2003 at 1:03 pm

    #449416

    The easiest and fastest I would think (not knowing if the tables are related) is to have an IF statement around the two selects.

    If @CurrentID > -1 OR IS NOT NULL

    Begin

    Select From Current

    End

    Else

    Begin

    Select From History.

    End

    Edited by - crappy on 02/20/2003 1:06:55 PM

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • February 20, 2003 at 5:07 pm

    #449441

    Writing a stored procedure with an IF statement will provide you with the most optimal execution in terms of time and resources.

    Using Dynamic SQL will lead to things like stored procedure recompiles, etc... which will impact on performance.

    Thanks

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • February 20, 2003 at 6:13 pm

    #449449

    IF you have to fit in one statement, here is how:

    DECLARE @current bit

    SET @current = 1 -- or 0

    SELECT ... FROM current_table

    WHERE @current = 1

    UNION

    SELECT ... FROM history_table

    WHERE @current = 0

  • February 24, 2003 at 8:07 am

    #449577

    Question to MSSQL gurus about mromm's answer...

    Using UNION instead of UNION ALL causes MSSQL to sort the results to remove duplicate rows. However, the WHERE condition of the 2 SELECT's will keep that from happening. Will MSSQL be smart enough not to sort the results anyway?

    Thanks,

    Greg

  • February 24, 2003 at 9:46 am

    #449587

    Greg,

    No - you are correct in that the UNION causes the duplicates to be removed. This is true even if all the results come from a single result set. In your example, if @current = 0, the result would be identical to

    SELECT DISTINCT ... FROM history_table

    Guarddata-

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply