Dynamic sql to retrieve volume count throwing incorrect syntax error

Question

I am getting incorrect syntax error at 'and Type=0 in the below dynamic sql. What I am trying to achieve is get the volume count of import activities between certain dates where the import id does not contain some companyId. I think I might be missing opening/closing quotes but not sure. Any help is appreciated. Thanks!

alter proc getVolumeCount
(@DatabaseName varchar(100),
@CompanyId as varchar(100),
@FromDate as varchar(100),
@ToDate as varchar(100)
)
as
begin
declare @SQL nvarchar(max)

set @SQL = 'use '+@DatabaseName+ '
select * from @Database.[dbo].[History]
where 1=1
and Cast(DateX as Date) between '+@FromDate+' and '+@ToDate
'and Type=0 --this is where I am getting incorrect syntax error
and Flags=3
and Id not in 
        (select ImportId from Item where CompanyId in
        (Select CAST(Item as int) from dbo.fn_SplitString('+@CompanyId+','',''))
        )'

exec sp_executesql @SQL

END

Answer 1

ALTER PROCEDURE getVolumeCount
(
    @DatabaseName VARCHAR(100),
    @CompanyId VARCHAR(100),
    @FromDate DATE,
    @ToDate DATE
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = '
    SELECT * FROM ' + QUOTENAME(@DatabaseName) + '.[dbo].[History]
    WHERE 1=1
    AND CAST(DateX AS DATE) BETWEEN @FromDate AND @ToDate
    AND Type = 0
    AND Flags = 3
    AND Id NOT IN 
    (
        SELECT ImportId FROM ' + QUOTENAME(@DatabaseName) + '.[dbo].[Item] 
        WHERE CompanyId IN 
        (
            SELECT CAST(Item AS INT) FROM dbo.fn_SplitString(@CompanyId, '','')
        )
    )'

    PRINT @SQL  

    EXEC sp_executesql @SQL,
        N'@CompanyId VARCHAR(100),
          @FromDate DATE,
          @ToDate DATE',
        @CompanyId = @CompanyId,
        @FromDate = @FromDate,
        @ToDate = @ToDate;
END

Used QUOTENAME(@DatabaseName) to prevent SQL injection and syntax issues.

@FromDate and @ToDate and @CompanyId should be passed as parameters to sp_executesql.

Consider using STRING_SPLIT or even a Table Valued Parameter for @CompanyId, and consider changing the column type of DateX to the correct DATE data type in the first place.