Modify dynamic SQL to include criteria on a pair of dates

I need to modify the following script to include a criteria that would limit the result set to a date span defined by two variables (@StartDate, @EndDate).

declare @cols as nvarchar(max), @query as nvarchar(max)

set @cols = STUFF((select distinct ',' + QUOTENAME(ParameterName)
            FROM [ServerName].[dbName].dbo.measurementParametersTb
            WHERE ParameterMerrickID IN (1007, 1006, 575)
            FOR XML PATH(''), Type
            ).value('.', 'nvarchar(max)')
            ,1,1,'')

set @query = 'SELECT Bolo_ID as Bolo_ID, RecordDate, ' + @Cols +
             'FROM
(
SELECT C.AccountingID as Bolo_ID, C.Wellname, 
MP.ParameterName, MR.RecordDate, MR.NumberValue
FROM [ServerName].[dbName].dbo.measurementRecordTb as MR
INNER JOIN (
select ParameterMerrickID, ParameterName 
from [ServerName].[dbName].dbo.measurementParametersTb
WHERE ParameterMerrickID IN (1007, 1006, 575)
) as MP ON mr.parameterid = mp.parametermerrickid
LEFT JOIN [ServerName].[dbName].dbo.CompletionTb as C
ON MR.ObjectMerrickType = 1 AND MR.ObjectMerrickID = C.MerrickID
WHERE isnull(MR.NumberValue, 0) <> 0
) x
Pivot
(
max(NumberValue) 
for ParameterName IN (' +@cols + ')
) p'

Execute(@query)

Open in new window

I tried this by adding two declaration statements, setting values for those, and then adding a couple of lines to the where clause:
AND MR.RecordDate >= @StartDate
AND MR.RecordDate <= @EndDate

Open in new window

which returned an error:

Must declare the scalar variable "@StartDate".

Then I tried:
AND MR.RecordDate >= ' + @StartDate + '
AND MR.RecordDate <= ' + @EndDate + '

Open in new window

which returned the error:

The data types nvarchar(max) and date are incompatible in the add operator.
LVL 51
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dorababu MSenior Software EngineerCommented:
I think you need to declare @StartDate  and @EndDate as you declare @cols
0
Dale FyeAuthor Commented:
I did.  I started the procedure with:
Declare @StartDate date
Declare @EndDate date

set @StartDate = '2018-08-25'
set @EndDate = '2018-08-31'

Open in new window

Which will eventually become parameters in a stored procedure.
0
ste5anSenior DeveloperCommented:
You need to embed the dates as (unambiguous) literals in your dynamic statement. E.g.

DECLARE @Columns AS NVARCHAR(MAX) = STUFF((   SELECT DISTINCT ', ' + QUOTENAME(ParameterName)
                                              FROM   [ServerName].[dbName].dbo.measurementParametersTb
                                              WHERE  ParameterMerrickID IN ( 1007, 1006, 575 )
                                              FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
                                          1 ,
                                          2 ,
                                          '');
DECLARE @EndDate DATE = '20180831';
DECLARE @StartDate DATE = '20180825';
DECLARE @Statement AS NVARCHAR(MAX) = '
    SELECT Bolo_ID as Bolo_ID ,
           RecordDate ,
           @Columns
    FROM   (   SELECT C.AccountingID as Bolo_ID ,
                      C.Wellname ,
                      MP.ParameterName ,
                      MR.RecordDate ,
                      MR.NumberValue
               FROM   [ServerName].[dbName].dbo.measurementRecordTb MR
                      INNER JOIN (   SELECT MP.ParameterMerrickID ,
                                            MP.ParameterName
                                     FROM   [ServerName].[dbName].dbo.measurementParametersTb MP
                                     WHERE  MP.ParameterMerrickID IN ( 1007, 1006, 575 )
                                    ) ON mr.parameterid = mp.parametermerrickid
                      LEFT JOIN [ServerName].[dbName].dbo.CompletionTb as C ON MR.ObjectMerrickType = 1
                                                                               AND MR.ObjectMerrickID = C.MerrickID
               WHERE  ISNULL(MR.NumberValue, 0) <> 0
                  AND MR.RecordDate >= @StartDate
                  AND MR.RecordDate <= @EndDate) Q
    PIVOT (   MAX(NumberValue)
              FOR ParameterName IN ( @Columns )) p
';

SET @Statement = REPLACE(@Statement, '@Columns', @Columns);
SET @Statement = REPLACE(@Statement, '@StartDate', '''' + FORMAT(@StartDate, 'yyyyMMdd') + '''');
SET @Statement = REPLACE(@Statement, '@EndDate', '''' + FORMAT(@EndDate, 'yyyyMMdd') + '''');
PRINT @Statement;
--EXECUTE ( @Statement );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeAuthor Commented:
ste5an,

Replace didn't work (realized I forgot to mention version of SS), but you got me pointed in the right direction.  Works like a charm with:

Cast(@StartDate as nvarchar(10))

Dale
0
ste5anSenior DeveloperCommented:
REPLACE() would work. Just FORMAT() is SQL Server 2012+ :)
0
Dale FyeAuthor Commented:
your right, it was the Format command.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.