How to Use a Stored Procedure Inside an SQL Query

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 SELECTs 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:
  1. "LinkedServer" - "LOCALSERVER". Note: all uppercase, no spaces
  2. Select "Other data sources"
  3. "Provider" - "Microsoft OLE DB Provider for SQL Server"
  4. "Product Name" - "SQLSERVER", again, all uppercase, no spaces
  5. "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.
  6. "Provider string" - blank
  7. "Location" - greyed out, blank
  8. "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:

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