Invalid column name in sql server with Dynamic SQL

Question

WHEN I try to run the SQL it is not formatting the @Status parameter' value with single quotes since it is text. Hence it is giving this error as Running is invalid column name.

DECLARE
    @ID int,
    @Status varchar(150),
    @StandardOutput varchar(max) = NULL,
    @StandardError varchar(max) = NULL,
    @Query Varchar(max),
    @S1 varchar(max),
    @S2 varchar(max),
    @S3 varchar(max)


SET     @Status = N'Running'
SET     @StandardError = N'So2234'
SET     @StandardOutput = Null
SET @S1 = ''
SET @ID = 1
--DECLARE @S1 varchar(max)
--SET @S1 = N' '
IF @Status IS NOT NULL 
    BEGIN
    SET @S1 = ( N',  dbo.JobQueue.Status = ' + @Status);
    END
IF @StandardError IS NOT NULL 
    BEGIN
    SET @S1 = @S1 + N',  dbo.JobQueue.StandardError = ' + @StandardError
    END
IF @StandardOutput IS NOT NULL
    BEGIN
    SET @S1 = N', dbo.JobQueue.StandardOutput = ' + @StandardOutput
    END

SET @S1 = (N' UPDATE  dbo.JobQueue SET ' + SUBSTRING(@S1, 2, LEN(@s1)) ) + ' ';
SET @S1 = @S1 + N' WHERE  dbo.JobQueue.ID = ' + CONVERT(VARCHAR(12), @ID);
SELECT @S1
EXEC(@S1)

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Running'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'So2234'

RESULT OF PRINT @S1

UPDATE  dbo.JobQueue SET   dbo.JobQueue.Status = Running,  dbo.JobQueue.StandardError = So2234  WHERE  dbo.JobQueue.ID = 1

Answer 1

Since these values are known by you and not provided by a UI somewhere, you can escape it manually. However, don't do this if the 'Running' value isn't constant but is provided by a UI (SQL Injection)

Try this:

IF @Status IS NOT NULL 
    BEGIN
    SET @S1 = ( N',  dbo.JobQueue.Status = ''' + @Status + ''');
...