Click here to Skip to main content
13,258,238 members (56,933 online)
Click here to Skip to main content
Add your own
alternative version

Stats

4.5K views
5 bookmarked
Posted 28 Oct 2017

How to Use a Stored Procedure Inside an SQL Query

, 28 Oct 2017
Rate this:
Please Sign up or sign in to vote.
How do you call a Stored Procedure inside a query and use the SELECT results? Tricky...or is it?

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:

  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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 4 Pin
dmjm-h30-Oct-17 11:38
memberdmjm-h30-Oct-17 11:38 
Question[My vote of 1] Way around SP Issue Pin
sunettew30-Oct-17 11:01
professionalsunettew30-Oct-17 11:01 
GeneralBad approach Pin
Rene Balvert30-Oct-17 4:10
memberRene Balvert30-Oct-17 4:10 
Questionother tip on this Pin
digimanus30-Oct-17 2:41
memberdigimanus30-Oct-17 2:41 
GeneralNEVER LINK SERVERS! Pin
PIEBALDconsult28-Oct-17 8:44
protectorPIEBALDconsult28-Oct-17 8:44 
GeneralRe: NEVER LINK SERVERS! Pin
pt140129-Oct-17 22:24
memberpt140129-Oct-17 22:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 28 Oct 2017
Article Copyright 2017 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid