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
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)
sp_executesql instead.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:
fk_Orgs_PropertyDetails_OrgID is already an INT then why not use fk_Orgs_PropertyDetails_OrgID = @OrgId directly and be done with it?sp_executesql solution.EXEC should also have worked, right?@OrgID).DECLARE @id INT = 1; EXEC ('SELECT * FROM(VALUES (1), (2)) t(Id) WHERE Id =' + @id);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
you are missing closing bracket
EXEC('Select Count(*) Total from '+ @TableName + ' where fk_Orgs_PropertyDetails_OrgID=' + 'Cast('+ @OrgID + ' as varchar)')
fk_Orgs_PropertyDetails_OrgIDisint? If so, why cast to varchar?