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

Question

I'm looking to filter the resultset of a stored procedure. What I'd like is something like the following (non-working) syntax:

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

edit - this is just one example, I'd like a general solution if possible

Answer 1

try this:

-- 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 could put the results of the stored procedure into a temporary table or table variable before this then query that table, applying whatever where clause to that 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'

You would, of course, change that final where clause to what you would want to filter on.

Answer 3

Move the stored procedure to a table-valued user defined function. Keep the existing stored procedure, but just have it call this new function rather than duplicating the logic. Then use the function in your query.

Answer 4

Assuming you want it for the exact purpose outlined in your question, and not a general solution, then you could just query sys.servers (SQL 2005+) (or sysservers pre 2005), no 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