June 20, 2013 at 11:46 am
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
June 20, 2013 at 12:03 pm
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 resultselect * , ................................ 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/
June 20, 2013 at 12:06 pm
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 resultselect * , ................................ 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
June 20, 2013 at 12:12 pm
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 resultselect * , ................................ 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/
June 20, 2013 at 12:19 pm
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 resultselect * , ................................ 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
June 20, 2013 at 12:22 pm
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 resultselect * , ................................ 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/
June 20, 2013 at 3:57 pm
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