Incorrect syntax near 'in'

Question

Why am I getting an error statement here with the following? incorrect syntax near 'in'?

INSERT INTO Staging.dbo.progress execute('SELECT ID
    FROM Staging.dbo.tempstudents WHERE StartTerm in' + @progressReportTerm);

Answer 1

Assuming that you are using SQL Server, execute does not take expressions. So you need to construct the query beforehand:

declare @sql nvarchar(max);

select @sql = '
SELECT ID
FROM Staging.dbo.tempstudents
WHERE StartTerm in ' + @progressReportTerm;

INSERT INTO ODS_Staging.dbo.progress
    exec(@sql);

You no doubt have a good reason for using dynamic SQL here. However, that is probably not really a requirement. And the dynamic SQL might be introducing a SQL injection risk. It is definitely introducing a potential for hard-to-debug syntax errors.