3 Replies - 606 Views - Last Post: 09 December 2016 - 02:18 AM

#1 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

How to query from Store Procedure ?

Posted 08 December 2016 - 02:01 AM

Hello,

I would like to query from Store Procedure. For example following below is I have success execute Store Procedure

USE [CHHB-WECARE]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[usp_ProductReport]
		@ProjectID = 1

SELECT	'Return Value' = @return_value

GO



But when I would like to query Store Procedure, it is error. How to do ?

select * from  (exec dbo.usp_ProductReport @ProjectID = 1)



Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.




Thank you for your help

PS: I am using MS SQL Server 2012

This post has been edited by tokei: 08 December 2016 - 02:03 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to query from Store Procedure ?

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2342
  • View blog
  • Posts: 9,393
  • Joined: 03-December 12

Re: How to query from Store Procedure ?

Posted 08 December 2016 - 05:39 AM

It's just, exec dbo.usp_ProductReport @ProjectID = 1

You dont select from it, it returns what it returns.
Was This Post Helpful? 0
  • +
  • -

#3 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

Re: How to query from Store Procedure ?

Posted 08 December 2016 - 07:52 PM

View Postastonecipher, on 08 December 2016 - 08:39 PM, said:

It's just, exec dbo.usp_ProductReport @ProjectID = 1

You dont select from it, it returns what it returns.


The code is just example. Because of the SQL code is very large, so I want to do separate query.

For example , i want to do like following below:

exec dbo.usp_ProductReport @ProjectID = 1
left join TableName



Hope this helps. Thanks
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,895
  • Joined: 12-December 12

Re: How to query from Store Procedure ?

Posted 09 December 2016 - 02:18 AM

To store the data returned from a stored procedure so that this data could participate in a join you could use a temporary table, like this:

create proc myproc
as 
begin
     select name from sysobjects
end
go

declare @t table (name varchar(100))
insert @t (name)
exec myproc

Actually, in this example that I found, @t creates a table variable and I don't know (haven't checked) whether this can be used with a join. In which case #t will create a temporary table instead.

Temporary Tables in SQL Server

Of course, an alternative is to create another stored procedure that returns the data that you actually need.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1