Dynamic query and use of variables

Question

The query below works if the 2 dates are hard coded, however, I would like to replace them with the 2 variables @FirstDayM and @LastDayM. When I do so, it returns the following error

"Conversion failed when converting date and/or time from character string"

DECLARE @sql varchar(max)
DECLARE @FirstDayM DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @LastDayM DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1);

    SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT COUNT(C1CustID) AS CertsCount, ''' + QuoteName(name)+  ''' as DBname FROM ' + QuoteName(name) + '.dbo.T_C1CustCourse'+
    ' WHERE C1CertificationDate_N >= '+'''2018-01-01'''+' AND C1CertificationDate_N <= '+'''2018-01-31'''
    FROM   sys.databases WHERE database_id > 4 AND state = 0;

EXEC (@sql);

Gordon Linoff · Accepted Answer · 2018-02-10 15:30:53Z

2

Use sp_executesql. Always. It makes it easy to put parameters into queries. Even if the dynamic query does not start with the parameter, you might decide to add one later.

declare @sql nvarchar(max);
declare @firstDayM date;
declare @lastDayM date;

set @firstDayM = ?;
set @lastDayM = ?;

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + '
SELECT COUNT(C1CustID) AS CertsCount, ''' + QuoteName(name)+  ''' as 
DBname
FROM ' + QuoteName(name) + '.dbo.T_C1CustCourse' + '
WHERE C1CertificationDate_N >= @FirstDayM AND C1CertificationDate_N 
<= @LastDayM'
FROM sys.databases
WHERE database_id > 4 AND state = 0;

EXEC sp_executesql @sql, N'@FirstDayM date, @lastDayM date',
     @FirstDayM=@FirstDayM, @lastDayM=@lastDayM;

edited Feb 10, 2018 at 15:30

answered Feb 10, 2018 at 14:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

DeepDiver Over a year ago

tnx a lot, does the @sql variable need to be declared too? I declared it NVARCHAR(MAX), however, it returns the following error 'Error converting data type nvarchar to date'

Gordon Linoff Over a year ago

@DeepDiver . . . Yes, of course. I missed copying it the first time.

DeepDiver Over a year ago

Tnx again, after declaring @sql nvarchar(max), it returns the following error 'Msg 8114, Level 16, State 5, Line 0 Error converting data type nvarchar to date.'

DeepDiver Over a year ago

It works, there are an unspotted @ symbol missing in the last line, @LastDayM=LastDayM

Collectives™ on Stack Overflow

Dynamic query and use of variables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related