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

could you share theresults when you PRINT @S1?

HLGEM
– HLGEM

2012-05-08 14:27:33 +00:00
Commented May 8, 2012 at 14:27 — HLGEM
– HLGEM, Commented May 8, 2012 at 14:27
I have updated with print @S1 Result.

Mitul
– Mitul

2012-05-08 14:29:27 +00:00
Commented May 8, 2012 at 14:29 — Mitul
– Mitul, Commented May 8, 2012 at 14:29

Oblivion2000 · Accepted Answer · 2012-05-08 14:31:52Z

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 + ''');
...

answered May 8, 2012 at 14:31

Oblivion2000

6264 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aF. · Accepted Answer · 2012-05-08 14:29:41Z

1

Check the result from SELECT @S1.

There must be a syntax error on it! For example, spaces or quotes missing somewhere usually are the main reason.

Update:

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

Running and So2234 prolly are text data types, so you're missing quotes on them!

answered May 8, 2012 at 14:29

aF.

67k45 gold badges141 silver badges200 bronze badges

2 Comments

Mitul Over a year ago

yes it is missing quotes but while setting @s1 it is specified in quotes and when it is getting generated as SQL it is removing them.

aF. Over a year ago

SET @Status = N'"Running"' SET @StandardError = N'"So2234"' Try changing to this

pkmiec · Accepted Answer · 2012-05-08 14:37:59Z

0

Why don't you use non-dynamic sql here?

UPDATE jq
SET Status = ISNULL(@status, jq.Status)
    ,StandardError = ISNULL(@StandardError, jq.StandardError)
    ,StandardOutput = ISNULL(@StandardOutput, jq.StandardOutput)      
FROM dbo.JobQueue jq
WHERE jq.ID = @id

answered May 8, 2012 at 14:37

pkmiec

2,69421 silver badges16 bronze badges

1 Comment

Mitul Over a year ago

Oh! So I don't have to worry about Null values this way. This should take care I guess right. This would be amazing.

Collectives™ on Stack Overflow

Invalid column name in sql server with Dynamic SQL

3 Answers 3

Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Related