Introduction
I was trying to get the average delta per weekday per user from a dataset which contains just the raw daily total, no delta information, and it was being a PITA - then I remembered an SP I wrote to generate the Deltas. But how do I call that from my query? Turns out, it's not too hard.
All I Have To Do Is EXEC, Don't I?
If only. EXEC
works, but you can't use the result set of the SP SELECT
s in your query, i.e., you can't do this:
SELECT * FROM EXEC MyDatabase.dbo.MyStoredProcedure;
You can try, but all you will get is a syntax error - because a procedure can return multiple result sets, each with its own schema, and SQL doesn't know what to do with them. So it throws a generic syntax error because it won't let you use EXEC
after a FROM
.
But ... there is a function OPENQUERY which will do what we want.
Except ... it only works to linked servers, not on the current server.
So, let's cheat...
First Off - Link Your Server
We are going to issue a query on our own server and use the result set.
So let's permanently set up a Link to ourselves, that way we don't need to do it each time we want to run the query (I'll be doing this on all my servers from now on as a matter of course)
- In SSMS, expand the "Server Objects" branch, and right click the "Linked Servers" twig.
- From the menu, select "New Linked Server..."
- In the dialog that comes up, set the following:
- "LinkedServer" - "LOCALSERVER". Note: all uppercase, no spaces
- Select "Other data sources"
- "Provider" - "Microsoft OLE DB Provider for SQL Server"
- "Product Name" - "SQLSERVER", again, all uppercase, no spaces
- "Data source" - enter your SQL Server instance name. For my development machine, that's "GRIFF-DESKTOP\SQLEXPRESS" but it's shown at the top of the Object Explorer pane in SSMS.
- "Provider string" - blank
- "Location" - greyed out, blank
- "Catalog" - blank
- Now click the "Security" page on the left of the dialog
- Select "Be made using the login's current security context"
- Press "OK"
Now You Can Use Your SP!
Try it:
SELECT * FROM OPENQUERY([LOCALSERVER], 'EXEC MyDatabase.dbo.MyStoredProcedure');
Just so you know, the whole query I ended up with looked like this:
SELECT LongName, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
FROM (SELECT mu.LongName, DATENAME(dw, DATEADD(dd, -1, a.EnterDate)) as dow, a.Delta as Delta
FROM OPENQUERY([LOCALSERVER], 'EXEC MyDatabaseName.dbo.spGetDatWithDelta') a
JOIN MonitoredUsers mu ON a.UserID=mu.ID
) x
PIVOT (AVG(Delta)
FOR dow IN (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
) p
ORDER BY LongName ASC
And it generates:
| LongName | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
1 | Lorem Ipsum | 33 | 47 | 39 | 37 | 40 | 33 | 27 |
2 | Dolor Sit | 91 | 132 | 129 | 121 | 123 | 103 | 80 |
3 | Amet Consectetur | 16 | 115 | 129 | 140 | 159 | 133 | 14 |
4 | Adipiscing Elit | 81 | 68 | 42 | 84 | 91 | 101 | 58 |
5 | Sed Do | 39 | 202 | 199 | 204 | 186 | 170 | 61 |
6 | Eiusmod Tempor | 496 | 851 | 856 | 840 | 863 | 782 | 552 |
7 | Incididunt Ut | 128 | 273 | 245 | 277 | 259 | 251 | 192 |
8 | Labore Et | 273 | 449 | 404 | 428 | 376 | 288 | 206 |
In case you are wondering, the date offset in the query is because the data is collected as-near-as-possible to 06:00 automatically, but is counted as being the previous day's data, although the time stamp is today. (This means I can also monitor how accurate the system is with running tasks at a particular time: "not very" is the answer.)
History
- 2017-10-28 - First version
- 2017-10-28 - Typos fixed, and final query added
- 2017-10-28 - Missed a few spaces, short explanation of query detail added