Sql-statement With Dynamic Table-names Or Redesign?

I have a MS SQL 2008 database which stores data for creating a weighted, undirected graph. The data is stored in tables with the following structure: [id1] [int] NOT NULL, [id2] [i

Solution 1:

SQL injection can easily be avoided in this case by comparing @tableName to the names of the existing tables. If it isn't one of them, it's bad input. (Obligatory xkcd reference: That is, unless you have a table called "bobby'; drop table students;")

Anyway, regarding your performance problems, with partitioned tables (since SQLServer 2005), you can have the same advantages like having several tables, but without the need for dynamic SQL.

Solution 2:

Maybe I did not understand everything, but:

CREATEPROCEDURE dbo.GetMyData (
     @TableNameASvarchar(50)
    )
ASBEGIN
    IF @TableName='Table_1'BEGINSELECT  id1
                   ,id2
                   ,[weight]
            FROM    dbo.Table_1
        END

    IF @TableName='Table_2'BEGINSELECT  id1
                   ,id2
                   ,[weight]
            FROM    dbo.Table_2
        ENDEND

and then:

EXEC dbo.GetMyData @TableName='Table_1'

A different technique involves using synonyms dynamically, for example:

DECLARE@TableNamevarchar(50)
SET@TableName='Table_1'-- drop synonym if it exists
IF object_id('dbo.MyCurrentTable', 'SN') ISNOTNULLDROP SYNONYM MyCurrentTable ;

-- create synonym for the current table
IF @TableName='Table_1'CREATE SYNONYM dbo.MyCurrentTable FOR dbo.Table_1 ;

IF @TableName='Table_2'CREATE SYNONYM dbo.MyCurrentTable FOR dbo.Table_2 ;

-- use synonymSELECT  id1, id2, [weight] 
FROM dbo.MyCurrentTable

Solution 3:

Partioned Table may be the answer to your problem. I've got another idea, that's "the other way around":

• each graph has it's own table (so you can still truncate table)
• define a view (with the structured you mentioned for your redefined table) as a UNION ALL over all graph-tables

I have no idea of the performance of a select on this view and so on, but it may give you what you are looking for. I'd be interested in the results if try this out ..