Parameterized query RRS feed

  • Question

  • I want to check if the temp table in sqlserver exists or not, if exists then drop and the create as follows:

    IF OBJECT_ID('#orders') IS NOT NULL DROP TABLE #orders

    SELECT * INTO #orders from orders WHERE 1=2

    But I need it through parameterized query in C#. I have tried as follows:

    SqlCommand sqlcmd = new SqlCommand("IF OBJECT_ID(@t) IS NOT NULL DROP TABLE @t", sqlcon);
    sqlcmd.CommandType = CommandType.Text;
    sqlcmd.Parameters.AddWithValue("@t", "#Orders");

    But it gives exception "Incorrect syntax near @t1"

    • Edited by puneet122016 Saturday, February 25, 2017 6:42 AM
    Saturday, February 25, 2017 6:42 AM

All replies

  • Saturday, February 25, 2017 12:34 PM
  • Nope. Table names cannot be parameterized in SQL Server. When you have to execute a statement where the table name is variable, you use dynamic sql, meaning that you concatenate the table name into a nvarchar that contains the rest of the query, and then you execute the resulting string with sp_executsql. But if you are doing this from a client written in C#, there isn't any advantage to doing the concatenation on the server side. You might as well concatenate the table name in the C# code and then use ExecuteNonQuery on the resulting string.

    By the way, be aware that a temp table #name only persists while the connection is open, and every connection sees a different table even if it also has the same #name. So you may have to rethink your code, it doesn't make sense to check whether #orders exists, because it can only exist if you just created it over the same connection that your client code is keeping open.

    Saturday, February 25, 2017 9:48 PM
  • Hi puneet,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help other members to find solution if they faces similar issue.

    Your understanding and cooperation will be grateful.

    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Monday, February 27, 2017 10:35 AM