Join based on tables that vary by row

问题:

Say I have a table Example with three columns: X, TableName, ColumnName.

TableName stores the name of a table in the database, and ColumnName stores the name of the column that I want to join X to. The problem is that different rows need to be joined to different tables (in a well defined way).

I want to be able to do something like:

Select *
from Example join TableName on X = ColumnName

Which is obviously not going to work.

I'm pretty new and did some googling and it seems like I might be able to use a cursor to loop over the rows and use dynamic SQL since the names of the Tables and Columns are stored in the database, but I've heard that both Cursors and Dynamic SQL should be avoided, so I haven't looked into the details of that. I was hoping there was a prefered way to solve this issue.

Thanks.

回答1:

Dynamic SQL can be slower than a stored proc, but not always, and has gotten much better as SQL Server has matured. Whenever possible, non-dynamic SQL should be used as it is cleaner and easier to maintain. However, dynamic SQL does have its place (ORM tools like Entity Framework and NHibernate use dynamic sql almost exclusively). Cursors should be avoided if at all possible, but SQL does support a WHILE loop that can be used instead.

DECLARE @Identity int = SELECT MIN(ex.Identity) FROM Example ex
DECLARE @TableName varchar(200)
DECLARE @ColumnName varchar(200)
WHILE @Identity IS NOT NULL BEGIN
    SELECT
        @TableName = ex.TableName,
        @ColumnName = ex.ColumnName
    FROM
        Example ex
    WHERE
        ex.Identity = @Identity

    /*
        build and execute your dynamic sql here
    */
    SET @Identity int = SELECT MIN(ex.Identity) FROM Example ex WHERE ex.Identity > @Identity
END