Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    28

    Question Unanswered: Calling a stored procedure within a stored procedure

    Hi folks,

    I created a stored procedure called td_calculated initially for testing in hopes to later create a function from it. Problem is I'm using a few temp tables which I later found out can't be used inside of a function.

    However I learned that it is possible to call a stored procedure within another one. I'm just a little fuzzy on the syntax

    Right now td_calculated accepts @case (int) parameter and just displays a single value @td(int). I'd like to know how to allow @td to be passed to another procedure and how to call this one.

    Can this be done within a column similar to a scalar function?

  2. #2
    Join Date
    Mar 2008
    Posts
    52
    I'm not sure if I understand your question but what about:

    Code:
    CREATE PROCEDURE dbo.TestProcedure
    (
        @something INT
    )
    AS
    BEGIN
        DECLARE @result INT
    
        EXEC @result = dbo.td_calculated @case = 2
    
    END
    GO

  3. #3
    Join Date
    Mar 2008
    Posts
    28
    Sorry let me be more specific. Technically I have two questions.

    Normally I would do this:
    SELECT
    case_num,
    'duration' = dbo.fn_td_calculated(case_num)
    FROM
    some_table

    Now when calling a stored procedure instead that's where I'm a little fuzzy.
    ie. 'duration' = dbo.sp_td_calculated ?????
    Not sure how to pass the parameter to sp_td_calculated and then receive the result.

    Secondly I'm not sure how to modify sp_td_calculated in order for it to send the result to the procedure calling it. Just for testing I've been displaying the output such as:

    SELECT result from #temp_table WHERE case_num = @case

    I know I could set a variable to this like:

    SELECT @result = (SELECT result from #temp_table WHERE case_num = @case)

    I'm just wondering about how @result needs to be declared in order for it to be returned by the procedure calling it.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When creating a procedure, add one extra variable as OUTPUT. Before calling the proc declare that parameter, and then pass it without initializing it. Inside the procedure assign the needed value to that OUTPUT parameter. Upon completion of execution that variable will have the value that the procedure assigned to it. This is similar to passing by reference or by address in front-end languages.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's wrong with using a UDF for this?
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nothing, if he replaces "create table #xxx" with "declare @xxx table". Otherwise, - he has to use a procedure with output parameters.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Mar 2008
    Posts
    28
    Quote Originally Posted by rdjabarov
    Nothing, if he replaces "create table #xxx" with "declare @xxx table". Otherwise, - he has to use a procedure with output parameters.
    Sweet, I'll give this a try. I'd rather user a UDF anyway. I just didn't know that made a difference. Thanks.

  8. #8
    Join Date
    Mar 2008
    Posts
    28

    Talking

    Thanks rdjabarov!

    Declaring a table variable worked great in the function!

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    As long as you don't "dump" a lot of data into those tables, - you should be fine. Also, make sure this function is not used somewhere in a join against a large table, because that will kill your tempdb, and consequently your app.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •