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

Hot Topic

Package Configurations not so portable?
Encrypted .dtsconfig files
About plug-in algorithms in SQL Server 2005
Failed to acquire connection / The AcquireConnection method Failed
How to allow remote connections to SQL Server 2005 Express database
Copying Database without data
The underlying connection was closed: An unexpected error occurred on a receive
Urgent: Oracle replication failed
"product level is insufficient" message during flat file import
Basic SQL Server Express Questions

SQL Server

File Drops
logging DDL Stmts
2 minutes after one datetime vs. another datetime
Simulate ROW_NUMBER () in SQL 2000
Backup SQLExpress database
How can I setup source connections with Sybase as Source database
Management Studio Express CTP update now available
SSIS Package failing to disconnect?
System.OutOfMemory Exception running MigrationWizard.exe
Multi-valued Parameter

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)

  • Adameis

    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


  • MSChuck

    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



  • Albert Ross

    This will store the result of an sp call

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



  • Michael Tang

    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.



  • Razter

    Nice!! Thnx Andreas.

  • Rpotthoff

    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



  • jehub

    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
  • User return value of a SP in another SP (SQL Server 2000)
Answer this question