Parametrized table name in SQL query

Question

I'm using Vb2005 to hit a SQL server. i have a pretty complicated query that hits identically structured databases on the server. I was looking into parameterizing the FROM clause but cant seem to do it. here is what i was trying

    Dim sql As String = "SELECT * " & _
                        "FROM [@DB].[dbo].[Trips] AS T " & _
                        "WHERE T.DepartTime >= CONVERT(DATETIME, 'Sep 08, 2011', 120);"

    Dim cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
    cmd.Parameters.Add("@DB", SqlDbType.Char)
    cmd.Parameters("@DB").Value = "DriverDb"

Depending on the users needs I will hit the 'DriverDb' or the 'MaintDb' or 'DispDb' databases. the SQL string is actually much more complicated than that with references to the db in about 5 places so wanted to simplify it so that i could just replace with a parameter.

thats under control. i use a different connection string per user request. — sinDizzy, Sep 09 '11 at 18:10
I think the comment meant "What about setting the Initial Catalog in the connection string. ex. Initial Catalog = DriverDb — brad.huffman, Sep 09 '11 at 21:37
Yeah i guess i could do that. I hadn't thought of that approach. once i do that then i have to do get rid of the @Db in "FROM [dbo].[Trips] AS T" correct? — sinDizzy, Sep 09 '11 at 21:54

score 2 · Answer 1 · answered Sep 09 '11 at 16:35

2

I guess we can't do that for the DB name or table name which may not be considered as parameters. My suggestion would be to use a variable "db" and append that to the string "sql" something like below

Dim db As String = "DriverDb";
Dim sql As String = "SELECT * " & _  
                    "FROM ["& db &"].[dbo].[Trips] AS T " & _                         
                     "WHERE T.DepartTime >= CONVERT(DATETIME, 'Sep 08, 2011', 120);"

Hope this helps!!

answered Sep 09 '11 at 16:35

Praveen

1,449
16
25

1

yeah that's what i have now, but I was just trying to make it cleaner by inserting the db name with a parameter. If you have a small query that's ok but with more complicated queries its a pain to not only code it but to change it later on. – sinDizzy Sep 09 '11 at 16:47
In the end i just used a regex – sinDizzy Sep 09 '11 at 17:39

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

The following question's answer seems to sum it up pretty well. Dynamic SQL (passing table name as parameter) You really should avoid dynamic SQL like this if at all possible.

edited May 23 '17 at 12:19

Community

1
1

answered Sep 09 '11 at 21:46

brad.huffman

1,281
8
12

The dynamic SQL could be avoided simply by a series of IFs. (+1 for avoiding dynamic SQL.) – Mark SQLDev Sep 09 '11 at 21:57
The databases that I'm hitting I have no control over. It just so happens that each database is identically structured and they hold data for different entities. Could it be revised to be one db with an id...yes but I cant control that part. but point taken, I just didn't want to write a complicated SQL where the only difference is the db name. – sinDizzy Sep 13 '11 at 15:33

Parametrized table name in SQL query

2 Answers2