Wrapping system procedure in View or Stored Procedure

Question

I am trying to call master..xp_fixeddrives from an application using .NET Entity Framework, so I need the results to be parsable. I am looking to call master..xp_fixeddrives, but for EF, it must be in a view or Stored Procedure.

I can't figure out the syntax to create this. I tried defining a view, and tried removing select and exec, but without luck. How can I do this?

create view zzz
as select exec 'master..xp_fixeddrives';

Also tried creating it as a stored procedure, but the SP doesn't actually return anything becuase it doesn't have a select statement.

CREATE PROCEDURE GetHardDriveFreeSpaceInMegabytes
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    EXEC master..xp_fixeddrives
END
GO

Simply calling the following doesn't work, either

select *
from master..xp_fixeddrives

Have you tried executing `GetHardDriveFreeSpaceInMegabytes`? Stored procedures don't need a `SELECT` statement to return anything. Calling another `EXEC` works fine. — Martin Smith, Jun 13 '13 at 15:29
possible duplicate of [How to SELECT \* INTO \[temp table\] FROM \[stored procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — Yuck, Jun 13 '13 at 15:30
The crux of your problem is solved by the question I linked above. — Yuck, Jun 13 '13 at 15:31
@Martin Smith executing it works in Management Studio, yes. However, when loading the procedure in the Entity Framework model, it indicates that the procedure doesn't return any columns. — Stealth Rabbi, Jun 13 '13 at 15:31
Well to fool EF you could change the definition to select a dummy result set with the correct columns. `SELECT N'C' AS drive, 1 AS [MB free]` Import into EF then `ALTER` the proc with the proper code. — Martin Smith, Jun 13 '13 at 15:33
@Yuck, the problem you linked seems fairly different. I'm not looking to do a temporary table. — Stealth Rabbi, Jun 13 '13 at 15:37
@StealthRabbi You kind of have to use a temporary table or a table variable (nearly the same thing). The answer you accepted is doing just that. — Yuck, Jun 13 '13 at 17:36
@Yuck but the duplicate you proposed is about creating a temp table without knowing the schema ahead of time. Which involves irrelevant hoops with loopback connecions and openquery. — Martin Smith, Jun 13 '13 at 22:55

score 2 · Accepted Answer · answered Jun 13 '13 at 15:53

To supply column metadata from the stored procedure consumable by Entity Framework you could do

CREATE PROCEDURE dbo.GetHardDriveFreeSpaceInMegabytes
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @xp_fixeddrives TABLE
    (
    drive nvarchar(1),
    [MB free] int
    )


    INSERT INTO @xp_fixeddrives 
    EXEC master..xp_fixeddrives

    SELECT *
    FROM @xp_fixeddrives
END

Wrapping system procedure in View or Stored Procedure

1 Answers1