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

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...` — S3S, Jul 07 '17 at 16:46
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, Jul 07 '17 at 16:53
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, Jul 07 '17 at 17:41

score 1 · Answer 1 · answered Jul 07 '17 at 16:52

1

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.

answered Jul 07 '17 at 16:52

Pரதீப்

91,748
19
131
172

Note `From openquery..` – Serg Jul 07 '17 at 16:59
Thanks Serg, I will try your recommendation. I've a feeling 1982 is the current date of 6/29/2017 minus 1,982 days. The first day the database was created. – joeldub Jul 07 '17 at 17:45
I have a feeling its 2017-6-29 because the date isn't quoted. Try it SELECT 2017-6-29 – bielawski Jul 07 '17 at 17:55
The working solution was a mix of both bielawski's and Prdp's suggestions. Thanks gentlemen. – joeldub Jul 08 '17 at 02:17
1

@joeldub - feel free to mark any one answer.. or post your solution and mark it as answer – Pரதீப் Jul 08 '17 at 02:19

bielawski · Answer 2 · 2017-07-07T18:53:29.267

1

I tested this. It has the correct quoting and it works. The problem was a combination of the wrong quoting and the unnamed date column.

DECLARE @maxdate DATE = '2017-6-29';
DECLARE @sql2 NVARCHAR(max) = 
    'SELECT * From openquery(MYSQL_donors, ''SELECT CONVERT(GROUP_CONCAT(btestDonor) USING utf8) AS DonorIDs, 
    '''''+CAST(@maxdate AS varchar(20))+''''' AS DDate, count(*) AS Count 
       FROM blood_tests WHERE btestResult = 1 
        AND btestDateResultBack >''''' +convert(varchar(30), @maxdate, 121)+''''''')'

EXEC (@sql2);

edited Jul 07 '17 at 18:53

answered Jul 07 '17 at 17:47

bielawski

1,466
15
20

Not sure what 'doesn't work' means since you don't ever say what result is expected or what happened after adding quotes. But I did update my answer to include adding a name to the column that displays the date because you are leaving it unnamed. Given the double hop on your query you probably need to double those quotes so I changed that too. – bielawski Jul 07 '17 at 18:14

Passing a parameter into a an Openquery select statement

2 Answers2