query results in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query results in stored procedure

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 09:59:31

how can i refer to the results of a query inside a stored procedure?

and use them later

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 10:03:57

you have to save it into a temporary table or reference it directly as a subquery

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:04:15

INSERT <YourTableNameHere> (<Column list here>)
EXEC <YourStoredProcedureNameHere>

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:04:56

You can use EXEC as a subquery?

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 10:08:49

here is my problem

running the query you helped me with peso -- i need to get the values out of that query combine them with some values that were passed and then insert that

what is the best way to do this?

how do you reference it directly as a subquery

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:13:22

You can write the query

SELECT TOP 1	ProjectTimePeriod
FROM		tblProject
WHERE		ProjectName = @ProjectName
		AND ProjectJobItem = @ProjectJobItem
		AND ProjectJobSubItem = @ProjectJobSubItem
		AND ProjectTimePeriod <= @ProjectTimePeriod
ORDER BY	ProjectTimePeriod DESC

as an UDF!
And then use the value from the UDF as any other variable or column.

CREATE FUNCTION	dbo.fnGetProjectTimePeriod
(
	@ProjectName VARCHAR(?),
	@ProjectJobItem INT,
	@ProjectJobSubItem INT,
	@ProjectTimePeriod DATETIME
)
RETURN INT
AS
BEGIN
	DECLARE	@ProjectTimePeriod INT

	SELECT TOP 1	@ProjectTimePeriod = ProjectTimePeriod
	FROM		tblProject
	WHERE		ProjectName = @ProjectName
			AND ProjectJobItem = @ProjectJobItem
			AND ProjectJobSubItem = @ProjectJobSubItem
			AND ProjectTimePeriod <= @ProjectTimePeriod
	ORDER BY	ProjectTimePeriod DESC

	RETURN @ProjectTimePeriod
END