Why does my query throw incorrect syntax error?

Question

I am trying to run this query by it throws error when run i.e.

EXEC('Select Count(*) Total
      from '+@TableName+'
      where fk_Orgs_PropertyDetails_OrgID='+ 'Cast('+@OrgID+' as varchar')

Why this error I get?

INCORRECT SYNTAX NEAR VARCHAR

Note: @TableName is Varchar(100) and @OrgID is int

I hope that `fk_Orgs_PropertyDetails_OrgID` is `int`? If so, why cast to varchar? — Hans Keﬆing, Jun 16 '16 at 06:20

Tim Biegeleisen · Answer 1 · 2016-06-16T07:00:26.043

8

You are missing a closing parenthesis in your call to CAST. Try this:

EXEC('Select Count(*) AS Total from ' + @TableName +
     ' where fk_Orgs_PropertyDetails_OrgID = Cast(' + @OrgID + ' as varchar)')

As @Felix pointed out, if your fk_Orgs_PropertyDetails_OrgID column is already an INT, then there is no need to cast @OrgID to VARCHAR. So just use the following:

EXEC('Select Count(*) AS Total from ' + @TableName +
     ' where fk_Orgs_PropertyDetails_OrgID = ' + @OrgID)

edited Jun 16 '16 at 07:00

answered Jun 16 '16 at 06:18

Tim Biegeleisen

502,043
27
286
360

I would add a suggestion to use `sp_executesql` instead. – Felix Pamittan Jun 16 '16 at 06:24

score 4 · Answer 2 · edited Jul 29 '20 at 18:40

4

As others have answered, you forgot a ) after the VARCHAR:

EXEC('Select Count(*) Total
      from '+@TableName+'
      where fk_Orgs_PropertyDetails_OrgID='+ 'Cast('+@OrgID+' as varchar)');

However, I suggest not using EXEC. Instead you should use sp_executesql. This has an additional benefit of providing more security by preventing SQL Injection.

DECLARE @TableName  VARCHAR(100),
        @OrgID      INT;

DECLARE @sql NVARCHAR(MAX);

SET @sql = 
'SELECT COUNT(*) Total 
FROM ' + QUOTENAME(@TableName) + '
WHERE fk_Orgs_PropertyDetails_OrgID = @OrgID';

EXEC sp_executesql
    @sql,
    N'@OrgID INT',
    @OrgID
;

The above assumes that fk_Orgs_PropertyDetails_OrgID is an INT.

Further reading on dynamic SQL:

The Curse and Blessings of Dynamic SQL by Erland Sommarskog
Bad Habits to Kick : Using EXEC() instead of sp_executesql by Aaron Bertrand

edited Jul 29 '20 at 18:40

Community

1
1

answered Jun 16 '16 at 06:45

Felix Pamittan

31,544
7
41
67

If `fk_Orgs_PropertyDetails_OrgID` is already an `INT` then why not use `fk_Orgs_PropertyDetails_OrgID = @OrgId` directly and be done with it? – Tim Biegeleisen Jun 16 '16 at 07:01
@TimBiegeleisen What do you mean? That's exactly what I've done in my `sp_executesql` solution. – Felix Pamittan Jun 16 '16 at 07:02
Yes, but in this case the original call to `EXEC` should also have worked, right? – Tim Biegeleisen Jun 16 '16 at 07:03
@TimBiegeleisen It still won't since you're concatenating a string (the query) with an int (the value of `@OrgID`). – Felix Pamittan Jun 16 '16 at 07:04
@TimBiegeleisen I might be wrong though. SQL Server might implicitly convert the variable. This query is working: `DECLARE @id INT = 1; EXEC ('SELECT * FROM(VALUES (1), (2)) t(Id) WHERE Id =' + @id);` – Felix Pamittan Jun 16 '16 at 07:07
I think `EXEC` can work with variables, q.v. [this SO question](http://stackoverflow.com/questions/7229070/how-to-set-value-to-variable-using-execute-in-t-sql). But I'm sure you're right that there is a better way to do it. – Tim Biegeleisen Jun 16 '16 at 07:09
@TimBiegeleisen Yes, `EXEC` can work with variables. I'm just not sure why it's not producing a syntax error when you concatenate a string and int inside the `EXEC`, but the select errors out: `DECLARE @id INT = 1; SELECT 'SELECT * FROM(VALUES (1), (2)) t(Id) WHERE Id =' + @id` – Felix Pamittan Jun 16 '16 at 07:12

Rajesh Ranjan · Answer 3 · 2016-06-16T07:21:56.563

In your query ")" is missing at last of CAST.

If @OrgID is defined varchar and fk_Orgs_PropertyDetails_OrgID is int then you dont need to use CAST. Execute Following

EXEC('Select Count(*) Total
from '+@TableName+'
where fk_Orgs_PropertyDetails_OrgID='+@OrgID)

If the column fk_Orgs_PropertyDetails_OrgID is varchar type. Execute Following

EXEC('Select Count(*) Total
from '+@TableName+'
where fk_Orgs_PropertyDetails_OrgID='''+@OrgID+'''')

Thanks

score 0 · Answer 4 · answered Jun 16 '16 at 06:24

0

you are missing closing bracket

EXEC('Select Count(*) Total from '+ @TableName + ' where fk_Orgs_PropertyDetails_OrgID=' + 'Cast('+ @OrgID + ' as varchar)')

answered Jun 16 '16 at 06:24

Nazir Ullah

610
5
13

Why does my query throw incorrect syntax error?

4 Answers4