include the outptut of storedprocedure in select

  • I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

  • ali.m.habib (6/20/2013)


    I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

    You can't use a stored proc like that. You would have to first execute calculateAverage and store the results, then you can reference it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

    You can't use a stored proc like that. You would have to first execute calculateAverage and store the results, then you can reference it.

    How to store it please , U mean in #table , but the main proc contains sum and many other complicated staf

  • ali.m.habib (6/20/2013)


    Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

    You can't use a stored proc like that. You would have to first execute calculateAverage and store the results, then you can reference it.

    How to store it please , U mean in #table , but the main proc contains sum and many other complicated staf

    I can't provide much assistance here because I have no idea what your code looks like or what you are trying to do.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

    You can't use a stored proc like that. You would have to first execute calculateAverage and store the results, then you can reference it.

    How to store it please , U mean in #table , but the main proc contains sum and many other complicated staf

    I can't provide much assistance here because I have no idea what your code looks like or what you are trying to do.

    in the main sp I calacualte the sum of the subjects compare it to another lookup table , then do another sum and get the difference

    I need to use this outptut in anothe stored procdeure

  • ali.m.habib (6/20/2013)


    Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    Sean Lange (6/20/2013)


    ali.m.habib (6/20/2013)


    I have stored prcuder like

    create proc calcaulateavaerage

    @studentid int

    as

    begin

    -- some complecated business and query

    return @result -- single decimale value value

    end

    and then I want to

    create proc the whole result

    select * , ................................ from X where X.value > (calcaulateavaerage X.Id)

    it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

    You can't use a stored proc like that. You would have to first execute calculateAverage and store the results, then you can reference it.

    How to store it please , U mean in #table , but the main proc contains sum and many other complicated staf

    I can't provide much assistance here because I have no idea what your code looks like or what you are trying to do.

    in the main sp I calacualte the sum of the subjects compare it to another lookup table , then do another sum and get the difference

    I need to use this outptut in anothe stored procdeure

    Can you post some actual code? Maybe your calculation can be turned into an iTVF? Again with no details I can't offer much help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here's an example where the output from one sp is passed into a second procedure which is run multiple times using dynamic sql. Maybe something here can help you.

    First create some sample data

    --create a table for some test data

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [StudentID] INT NULL,

    [AmtDue] MONEY NULL,

    [DueDate] DATETIME,

    PRIMARY KEY (ID))

    --generate the test data

    INSERT INTO #TempTable

    SELECT TOP 10000

    StudentID = (SELECT (ABS(CHECKSUM(NEWID()) % 100) * 1))+1,

    AmtDue = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    DueDate = DATEADD(year,10,CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME))

    FROM Master.dbo.SysColumns t1

    CROSS JOIN Master.dbo.SysColumns t2

    GO

    Now create the test procedures which demonstrate the method

    --FIRST procedure takes a single ID as input and outputs the amt due

    CREATE PROCEDURE dbo.Test1

    @StudentID INT

    ,@AmtDue MONEY OUTPUT

    AS

    BEGIN

    DECLARE

    @DaysOverDue INT

    SELECT

    @DaysOverDue =

    DATEDIFF(DAY,GETDATE(),MAX(tt.DueDate))

    FROM #TempTable AS tt

    WHERE

    StudentID = @StudentID

    IF @DaysOverDue >= 0

    BEGIN

    SELECT

    @AmtDue = SUM(AmtDue)

    FROM #TempTable

    WHERE StudentID = @StudentID

    RETURN @StudentID

    END

    ELSE

    BEGIN

    SET @AmtDue = 0

    RETURN 0

    END

    END

    GO

    --SECOND procedure uses the OUTPUT of the first procedure

    CREATE PROCEDURE dbo.Test2

    @StudentID INT

    ,@BalanceDue MONEY

    AS

    BEGIN

    SELECT DISTINCT

    @StudentID AS StudentID

    ,@BalanceDue AS BalanceDue

    FROM #TempTable

    WHERE

    StudentID = @StudentID

    END

    GO

    Now create a temp table to hold the results

    and run the procedures

    --a table to hold the results

    IF OBJECT_ID('tempdb..#ResultsTable') IS NOT NULL

    DROP TABLE #ResultsTable

    CREATE TABLE #ResultsTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [StudentID] INT NULL,

    [BalanceDue] MONEY NULL,

    PRIMARY KEY (ID))

    GO

    DECLARE

    @maxID INT

    ,@strSQL NVARCHAR(MAX)

    SELECT

    @maxID = MAX(StudentID)

    FROM

    #TempTable

    --Run the procedures using dynamicSQL

    SELECT

    @strSQL =

    (SELECT

    (SELECT CHAR(10)+N'EXEC @ReturnValue = dbo.Test1 '+CAST(t.N AS NVARCHAR(10))+', @OutputParameter OUTPUT'+CHAR(10)+

    'INSERT INTO #ResultsTable EXEC dbo.Test2 @ReturnValue, @OutputParameter'+CHAR(10))

    FROM

    dbo.Tally AS t

    WHERE

    t.N <= @maxID

    FOR XML PATH(''))

    SET @strSQL = N'DECLARE @ReturnValue INT, @OutputParameter MONEY'+CHAR(10)+@strSQL

    --PRINT @strSQL

    EXEC(@strSQL)

    SELECT * FROM #ResultsTable

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply