Trying to rewrite a stored procedure so it is possible to pass on a @tableName from C#

Question

I am trying to pass a table name to a stored procedure in SQL Server.

I have understood that this is not possible by default to do because of risks for injection malicious attacks.

When googled around, I have understood that this is anyway possible to do.

I will try to create this anyway as I have understood that it is possible somehow by declaring a string query like I try to do below. Knowing about the risks, I will try to do this anyway as I really like to create all code in procedures to make it more clean. The code will also only be used on my own computer.

The original code that I have for the stored procedure looks like this. I have put @tableName here as a parameter which then doesn't work as it isn't possible be default to pass on a tableName here.

CREATE PROCEDURE getLastFeatureUpdate
    @maxDateTime BIGINT = 0,
    @tableName nvarchar
AS
    SELECT
        test.FeatureNbr,
        test.DayTime,
        test.Val
    FROM
        @tableName test 
    WHERE
        DayTime = (SELECT MAX(DayTime)
                   FROM @tableName
                   WHERE FeatureNbr = test.FeatureNbr 
                     AND DayTime <= @maxDateTime)

Now comes the version, I am trying to do in order to re-write the above code so it will work by passing on the @tableName parameter - I get 2 errors for this code:

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@maxDateTime"

EXEC sp_executesql @FullQuery shows error:

Error: Procedure: [dbo].[getLastFeatureUpdate] has an unresolved reference to object [dbo].[sp_executesql]

I then wonder what I can be doing wrong in my code?

Rewritten stored procedure:

CREATE PROCEDURE getLastFeatureUpdate
    @maxDateTime BIGINT = 0,
    @tableName nvarchar
AS
BEGIN
    DECLARE @FullQuery nvarchar(1000)

    SET @FullQuery = N'SELECT test.FeatureNbr, test.DayTime, test.Val FROM ' + QUOTENAME(@tableName) + ' test 
                     WHERE DayTime = ( SELECT MAX(DayTime) FROM ' + QUOTENAME(@tableName) + ' WHERE FeatureNbr = test.FeatureNbr AND DayTime <= @maxDateTime )'

    EXEC sp_executesql @FullQuery
END

EDIT:

C# code to execute the Stored Procedure:

        void getLastFeatureUpdate()
        {
            using (SqlConnection conn = new SqlConnection(GetConnectionString()))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("getLastFeatureUpdate", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@maxDateTime", 201010222045));
                cmd.Parameters.Add(new SqlParameter("@tableName", "testTable"));

                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        Int64 v1 = (Int64)rdr["DayTime"];
                        int v2 = (Int16)rdr["FeatureNbr"];
                        double v3 = (double)rdr["Val"];

                        MessageBox.Show(v1 + "," + v2 + "," + v3);
                    }
                }
            }
        }

Answer 1

You have to define and pass in the parameters when using sp_executesql for a query that takes parameters:

EXECUTE sp_executesql 
    @FullQuery, 
    N'@maxDateTime float(53)',  
    @maxDateTime ; 

I will say that using a dynamic table name is a sign that you have a bad table design, specifically when you have similar data split into multiple tables rather than a single table with columns to categorize the data.