Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

include the outptut of storedprocedure in select Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 11:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #1465871
Posted Thursday, June 20, 2013 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465875
Posted Thursday, June 20, 2013 12:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #1465877
Posted Thursday, June 20, 2013 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465882
Posted Thursday, June 20, 2013 12:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #1465891
Posted Thursday, June 20, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465892
Posted Thursday, June 20, 2013 3:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1465973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse