Filter the result set of a stored procedure using the where clause

Question

I am looking for a filter to set the results of a stored procedure. What I would like is something like the following (inoperative) syntax:

IF EXISTS ( SELECT 1 FROM  (EXEC  sp_linkedservers) WHERE srv_name = 'myServer' )
    PRINT N'dropping linked servers'
GO

edit is just one example, I would like, if possible, a general solution

Answer 1

try the following:

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

select * into #t from openquery(loopback, 'set fmtonly on exec sp_who') 
select * from #t
drop table #t
go

Answer 2

You can put the results of the stored procedure in a temporary table or table variable before that, then query that table using any where clause you want.

[Edited]

Like this:

DECLARE @foo TABLE
(
SRV_NAME NVARCHAR(100),
SRV_PROVIDERNAME NVARCHAR(100),
SRV_PRODUCT NVARCHAR(100),
SRV_DATASOURCE NVARCHAR(100),
SRV_PROVIDERSTRING NVARCHAR(100),
SRV_LOCATION NVARCHAR(100),
SRV_CAT NVARCHAR(100)
)

INSERT INTO @foo
EXEC  sp_linkedservers

SELECT * FROM @foo WHERE SRV_PRODUCT = 'SQL Server'

Of course, you would change this final where clause to what you would like to filter.

Answer 3

Assuming you want to specify it exactly in your question, and not a general solution, then you can just query sys.servers (SQL 2005+) (or sysservers pre 2005), you do not need to use the sp_linkedservers stored procedure

-- SQL 2005+
IF EXISTS ( SELECT 1 FROM sys.servers WHERE name = 'myServer' )
    PRINT N'dropping linked servers'
GO

-- SQL 2000
IF EXISTS ( SELECT 1 FROM sysservers WHERE srvname = 'myServer' )
    PRINT N'dropping linked servers'
GO

Answer 4

Move the stored procedure to the function defined by the table. Store an existing stored procedure, but just ask it to call this new function, not duplicate the logic. Then use the function in the query.