Calling a Procedure when trying to create a variable within a query - TSQL

Hi All,
I am wondering if this is possible.
I understand how to create a variable within my query - when I am assigning a value from a table query to the variable. However I am wondering is it possible to instead of pulling the value from a query to actually pull it from a stored procedure?

For Example:
DECLARE  @Payout  Float

SELECT @Payout = SUM(revenue)
FROM
(EXEC  [dbo].[mystoredprocedure] '2014-09-01','2014-10-01')SUBQ

SELECT @Payout

Thank you,
P
PutochAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PutochAuthor Commented:
I think I may have answered my own question.
By doing the following it allowed me to call the procedure.

DECLARE  @Payout  Float
EXEC @Payout  =[mystoredprocedure] '2014-09-01','2014-10-01'

hope this can help someone else.
0
PutochAuthor Commented:
Actually I didn't!
That only returned all the rows from the SP. Instead of the actual value I needed

DECLARE  @Payout Float
SELECT @Payout = Payout
EXEC @Payout =[mystoredprocedur] @fromdate,@todate
0
Haris DulicIT ArchitectCommented:
Hello,

if you can user the temp table you can store the values to temp table and then assign it to your parameter i.e.

DECLARE @Payout Float

CREATE TABLE #tmpvalue
(
    TEMPpayout float
)
INSERT INTO #tmpvalue(TEMPpayout )
EXEC dbo.mystoredprocedur @fromdate,@todate

SELECT
    @Payout = TEMPpayout 
FROM 
    #tmpvalue

Open in new window

0
Scott PletcherSenior DBACommented:
As samo4fun noted, you can't do that directly.

Another possibility: add optional parameters to the proc to "tell" it to return only the total, and then use those params when you EXEC the proc:


CREATE PROCEDURE  mystoredprocedure
    @...existing params...,
    @return_revenue_total_only bit = 0,
    @revenue_total float = 0 OUTPUT
AS
...existing proc code...
IF @return_revenue_total_only = 1
    SELECT @revenue_total = SUM(revenue) FROM (...existing SELECT to return result set from proc...)
ELSE
    ...existing SELECT to return result set from proc...
GO



DECLARE @Payout  Float
EXEC  [dbo].[mystoredprocedure] '2014-09-01','2014-10-01', @return_revenue_total_only = 1,
    @revenue_total = @Payout OUTPUT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
thank you both for the feedback. I ended up using a global variable and that seemed to do the trick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.