CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DeepDiver
DeepDiver

Reputation: 139

Dynamic query and use of variables

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);

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 2

Related Questions