Passing a parameter into a an Openquery select statement

Question

I've a query:

DECLARE @sql2 nvarchar(max), @counts int, @maxdate date;

SELECT @maxdate=isnull(Max(btestDateResultBack),'01/01/1900') FROM BloodTests
PRINT @maxdate
print @maxdate

SET @sql2 = 'SELECT CONVERT(GROUP_CONCAT(btestDonor) USING utf8) AS DonorIDs, 
            '+CAST(@maxdate AS varchar(20))+', count(*) AS Count 
             FROM blood_tests WHERE btestResult = 1 
             AND btestDateResultBack >''''' +convert(varchar(30), @maxdate, 121)  
             +''''''

SET @sql2 = 'SELECT * From openquery(MYSQL_donors, '''+@sql2+''')'

EXEC sp_executesql @sql2

that returns the DonorIDs and Count columns correctly, but the column with the variable '+CAST(@maxdate AS varchar(20))+' returns the date as an alias to a column (see image below).

Using @maxdate in the WHERE clause works just fine.

How can I use @maxdate in the body of the SELECT statement?

I've seen plenty of answers on how can I use this variable in a WHERE clause, but I haven't seen one discussing adding it to the SELECT statements select list.

Final Working Solution

DECLARE @sql nvarchar(max) ,@sql2 nvarchar(max) , @sql3 nvarchar(max) , @counts int , @bloodtestdonors int , @maxdate date;

--Find and delete bloodtests from today SELECT @maxdate = isnull(Max(btestDateResultBack),'1900-01-01') FROM BloodTests;

PRINT @maxdate;

SET @sql2 = 'SELECT CONVERT(GROUP_CONCAT(btestDonor) USING utf8) AS DonorIDs, '''''+CAST(@maxdate AS varchar(30))+''''' AS Date, count(*) AS Count FROM blood_tests WHERE btestResult = 1 AND btestDateResultBack >'''''+convert(varchar(30), @maxdate, 121)+''''''

SET @sql2 = 'SELECT * From openquery(MYSQL_donors, '''+@sql2+''')'

EXEC sp_executesql @sql2

Thanks @bielawski and @Prdp for the help and clarification. I appreciate it. Thanks @stan-shaw for the code formatting.

have you tried ...AS DonorIDs, '+CAST(@maxdate AS varchar(20))+' as SomeDate... — scsimon
It's OK, 2017-06-29 = 1982. If you need the 2017-06-29 as a text value, you need extra quotes around it and an alias. — Serg
Hi @scsimon, I tried that too. :D The alias overwrites the the data in the header in the column, e.g., "'+CAST(@maxdate AS varchar(20))+' AS MaximumDate" Yields "MaximumDate/1982" — joeldub

Pரதீப் Pரதீப் · Accepted Answer · 2017-07-07T16:52:10

Use sp_executesql to pass the parameter values to your query

SET @sql2 = 'SELECT CONVERT(GROUP_CONCAT(btestDonor) USING utf8) AS DonorIDs, 
             @maxdate, count(*) AS Count 
             FROM blood_tests WHERE btestResult = 1 
             AND btestDateResultBack > @maxdate'

exec sp_executesql @sql2,N'@maxdate date',@maxdate = @maxdate

What I don't understand is, how you that values 1982.

Passing a parameter into a an Openquery select statement

2 Answers