First, I'll start by describing the problem domain:
Stored procedures are meant to be exactly what their name implies, a stored script that can be run on demand using parameters to perform a repeatable task. The literal meaning of procedure is "an established or official way of doing something".
Stored procedures are great, but all too often, people use them as a crutch for not spending the time or effort to take their linear thinking and break it down into a more appropriate flattened structure, like conditional joins and common table expressions. My personal rule of thumb is that if the end goal of the script you are writing is to generate a single dataset, then it is not a stored procedure, it's a view or a table-valued function.
Because the result set of a stored procedure can be something more than a single dataset, often times, third-party tools are not capable of using the output of an SP directly. This is how we end up with point-in-time reporting, nightly agent jobs and a heap of UD tables, or as I like to call it "Data Sprawl". Now, what was meant to return a single dataset for one of your managers has now generated several objects and a job that has to be monitored just to determine how many widgets didn't have their names entered during setup which is silly.
"But Josh, sometimes you don't have a choice but to use an SP, especially when using heterogeneous data sources!"
WRONG, kind of....
Enter the loopback linked server.
What is it?
Its a linked server,, that loops back, obviously :-)
But seriously, that's all it is. All you have to do is create a linked server and under the name parameter just use [.]
EXEC master.dbo.Sp_addlinkedserver
@server = N'.',
@srvproduct=N'SQL Server'
"Okay, but how is that helpful?"
I'm glad you asked!
In the scenario that you have to create a stored procedure for one reason or another, if you have a loopback linked server, you can call out the loopback server in an openquery or openrowset and execute the SP or even the raw script in the command text, and as long as the return is a single data set, the server will see it just as it would a view or table, which allows it to be run on demand by a user in any third party application without worrying about it's support.
You can even use your openquery in a view too.
CREATE VIEW v_rpuser
AS
SELECT *
FROM Openquery([.], 'set nocount on; set fmtonly off;
declare @rpuser as table ( FCUNAME varchar(20), FCINITIALS varchar(6), FCCOMPID varchar(5), FCMODID varchar(20), FCACCLVL varchar(15), FCDESC varchar(max), FCMODULE varchar(30), FCTYPE varchar(20), FNLEVEL INT, SrtOrdr INT )
insert into @rpuser
exec m2msystem.[dbo].[ExecuteReportProc] 294351
select * from @rpuser ') a
In the above example, I first shut all the other query outputs off using the SET commands, then I'm declaring a table variable, inserting data into it from a stored proc then selecting the data all INSIDE of a view!
This completely eliminates the need for there to be a job to refresh the data and allows you to have the data available live. Be sure not to use global temp tables though. They can cause some interference if more than one user is executing it.
This is invaluable when using datasets from multiple sources where you need to keep the output as a view.
