User return value of a SP in another SP (SQL Server 2000)

Hot Topic

Noob questions about Reporting Services
sql server express does not start
Report builder, filter and SSAS
Protection Level 5 SQL Server storage encryption
Feedback Requested - What Content Do You Want to see for SQL Server Express?
Chain transact sql scripts
Large objects and the transaction log
Tell Us Your Express Story and Win $100!
SqlCeRemoteDataAccess Pull issue with ConnectionString
AS2005 September CTP Memory Error

SQL Server

HELP! Problem in my code
Problem with Large data amounts
Variables Not Seen In Custom DataFlow Destination
Query Load Balancing
A/Synchronous execution of SSIS ETL packages
Drill through in migrated cubes ?
Task Expression Evalutation Time
graphics appears in false position.
Why do all packages in the project open when running one in debug mode?
I hate this

User return value of a SP in another SP (SQL Server 2000)

Hi group,

I need the result of a SP in another SP. The SP I need to have the result of does not have an output param. It just returns a result.

I tried the following code:
SELECT @WeightedAverage = EXEC uspCalcWeightedAverage @StartDate, @EndDate, @InMarket, @InProductType, @InWeekDay

I get the following error:
Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'EXEC'.

Is it possible to fill a variable with the result of a SP

TIA

Regards,

Sander

Answer this question

User return value of a SP in another SP (SQL Server 2000)

  • Olusola Abiodun

    If you want the procedure to return an output value, why can't you just do that, and have the proc return the average as an output variable..
    That is how it's supposed to be done, and then it will work for you :)

    /Kenneth

  • Jack Knife33706

    Hmm,

    doesn't seem to work after all:
    DECLARE @WeightedAverage AS numeric(38,36)
    EXEC @WeightedAverage = uspCalcWeightedAverage @StartDate2, @EndDate2, @InMarket, @InProductType, @InWeekDay
    SELECT @WeightedAverage

    Results:
    0.98699235596153856

    .000000000000000000000000000000000000

    It seems that the variable @WeightedAverage is empty... Any idea why


  • borge3000

    This will store the result of an sp call

    declare @WeightedAverage int;
    execute @WeightedAverage = uspCalcWeightedAverage @StartDate, @EndDate, @InMarket, @inProductType, @InWeekDay;



  • Ameet Bala

    I have not compared SP vs UDF performance or am aware of any performance issues.

    What I do know is that everything is not possible in a UDF, like dynamic SQL and calling SPs.

    They are basically designed to do what you need to do. Calculate a value from a given set of parameters, probably worth for you to look into.



  • Joseph Ollero

    Stored procedures always return an integer.

    If you have a numeric(38,36) inside the procedure and tries to return it it will get casted to a integer.

    Can't you use a function instead



  • Brian Sutherland

    We were also thinking in that direction.
    Speed (performance) is of the essence here (not development speed...)

    Is there a performance penalty when I use a UDF for this



  • Lyle.Dodge

    Nice!! Thnx Andreas.
  • User return value of a SP in another SP (SQL Server 2000)
Answer this question