1

I have a stored procedure which returns a table of data. I need to query certain data from this results.

currently I am doing it as follows:

Create Table    #proc  
(
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(50)  NULL,
    [MetricName] NVARCHAR(50)  NULL,
    [Value] NVARCHAR(50)  NULL,
    [PointsAwarded] NVARCHAR(50)  NULL,
    [MaxPoints] INT NULL,
    [wEIGHTAGE] DECIMAL NULL
)

Insert into #proc  
exec dbo.prc_ShopInstanceCustomersData 2023, 10000 

select Name, SUM(Case when [PointsAwarded] = 'n/a' then 0 else [PointsAwarded] end) As TotalPoints from #proc group by Name

I dont want to use another extra table here for stored proc results. Is there a way to execute the select query directly from stored procedure results without fetching the results into a table?

Thanks in advance...

mmssaann
  • 1,507
  • 6
  • 27
  • 55
  • 1
    Nope...............Why don't you want to use a temp table? That;s what they are there for! – Mitch Wheat Sep 04 '13 at 06:23
  • If you need this query frequently, you could create a new stored procedure that returns just those columns you need – marc_s Sep 04 '13 at 06:31
  • See also this link: [Is it possible to use a Stored Procedure as a Subquery?](http://stackoverflow.com/questions/4889584/is-it-possible-to-use-a-stored-procedure-as-a-subquery-in-sql-server-2008). Depending on your requirements and your constraints, you might also consider writing a different stored procedure, and/or use views instead. – paulsm4 Sep 04 '13 at 06:31
  • I wrote a query in linq to sql in application to handle this. – mmssaann Sep 04 '13 at 09:14

0 Answers0