C# SqlDataAdapter SelectCommand: pass in parameter into TSQL 'execute'

Question

From withing C#, I want to run an SQL command (business intelligence) based on the SQL Server version. So the command is something like

if newVersion
    SELECT some cool stuff
else
    SELECT some old stuff

Since the new version contains functionality that is not available in old SQL Server versions, the only way I found to do this was via the execute function in TSQL

if newVersion
    execute('new SELECT')
else
    execute ('old SELECT')

So far so good.

I also want to pass in a parameter - actually a DateTime that will be part of the WHERE clause of the SELECT.

To make things even more interesting, I will pass the DateTime @StartDate from C# code via an SqlDataAdapter that passes a .net string statement as the TSQL text into the SelectCommand and calls Fill. Something like

adapter.SelectCommand = new SqlCommand(statement, con);
adapter.SelectCommand.Parameters.Add(myStartDate);
adapter.Fill(ds, resultTableName);

But.. for the life of me, I couldn't get it into the SQL to work. I always get some syntax error exception when calling the Fill method (executing the code).

Both passing in parameters (to no dynamic SQL) as well as hard coding a parameter into the dynamic SQL works. But not the combination.

My SQL (just for testing) looks something like

EXECUTE('SELECT ''' + @MyStartDate + '''
')

Where I try to convert the parameter into part of the SQL command text that is to be executed on the server.

I get

System.Data.SqlClient.SqlException: Incorrect syntax near SELECT

I want to use the parameter added from C# in the SQL query that is executed via the execute command.

Answer 1

As I said in the comment, don't use EXEC ({SQL Statement/Variable}) when using dynamic SQL, instead use sys.sp_executesql which can be parametrised.

We don't have any real example code here, so I'm going to use your overly simplified statement, which would be:

DECLARE @StartDate date = GETDATE();

DECLARE @SQL nvarchar(MAX) = N'SELECT @StartDate;';
EXEC sys.sp_executesql @SQL, N'@StartDate date', @StartDate = @StartDate;