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
– sinDizzy, Commented Sep 9, 2011 at 18:10
I think the comment meant "What about setting the Initial Catalog in the connection string. ex. Initial Catalog = DriverDb — brad.huffman
– brad.huffman, Commented Sep 9, 2011 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
– sinDizzy, Commented Sep 9, 2011 at 21:54

Praveen · Accepted Answer · 2011-09-09 16:35:01Z

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 9, 2011 at 16:35

Praveen

1,44916 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sinDizzy Over a year ago

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 Over a year ago

In the end i just used a regex

Community · Accepted Answer · 2017-05-23 12:19:03Z

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, 2017 at 12:19

CommunityBot

11 silver badge

answered Sep 9, 2011 at 21:46

brad.huffman

1,2718 silver badges12 bronze badges

2 Comments

Mark SQLDev Over a year ago

The dynamic SQL could be avoided simply by a series of IFs. (+1 for avoiding dynamic SQL.)

sinDizzy Over a year ago

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.

Collectives™ on Stack Overflow

Parametrized table name in SQL query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related