Execute stored procedures within a procedure
-
I have a stored procedure that creates several sets of temp tables to get the final resultset. Each statement creating the temp table requires parameters so a series of views won't work. I also use these same statements in other stored procedures. Can a stored procedure be executed within another stored procedure with the results in a temp table?
-
sure!
IN BOL:
quote:
...A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table.
...
HTH
* Noel -
My problem seems to be getting the correct syntax to execute the stored procedure to get the temp table from within another procedure.
-
CREATE PROCEDURE Test2 -- Modify Temp Values
AS
UPDATE #t Set x = 2
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT )
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
-- Execute To Test
EXEC Test1
Results:
Test1Col
-----------
1
Test2Col
-----------
2
HTH
* Noel -
I am probably trying to do something I can't.
1. I have a query that inserts a recordset into a Temp table, #Temp1
2. I have another query that joins another table to #Temp1 to get another table, #Temp2.
3. The final query joins with another table, #Temp1, and #Temp2 for the recordset to be returned by the stored procedure.
All of the queries require parameters to nesting them in a view will not work.
I uses these queries in several procedures and I was wanting to store each query in a procedure, passing the parameters, that will create the temp table needed in the subsequent queries. I need to execute each procedure in order and use the temp table created by it later in the main procedure.
You example will work if I want to have the select statement for each query in the main procedure. I want to be able keep the select statement in separate procedures since it is used in other procedures as well. Calling it and getting a temp table to join with.
Can this be done, and how?
-
You haven't show me any examples but I think
you can create 3 separated table-valued functions based on parameters.
with that in mind you can use those as Parameterized Views
and your statement could be crafted as
SELECT
Val1,Val2,...
FROM
dbo.fn1(@Param1,@Param2) T1
INNER JOIN
dbo.fn2(@Param3,@Param4) T2
ON T1.key = T2.key
INNER JOIN
dbo.fn3(@Param5,@Param6) T3
ON T2.key = T3.key
WHERE
...
that way you can reuse your Parameterized views else where.
* Noel -
This is working great! I have not utilized function like this and getting parametized views really helps.
Now, I have a new twist on it. I need to use a field in a table that I am joining to the function as the parameter:
LEFT OUTER JOIN (SELECT * FROM dbo.fnAssessmentsByDateRange(dbo.Progress_Notes.Date_)) Assessments
on dbo.Progress_Notes.Consumer_Id = Assessments.ConsumerID
I keep getting an error in using the field as the parameter for the function.
Server: Msg 170, Level 15, State 1, Line 91
Line 82: Incorrect syntax near '.'.
Can I not use a field in the joined table to pass the value to the function?
Thanks
-
I was wanting to do the same thing some time ago... I realized that Table Function parameters can NOT be values from a "Joined" table. I think is is because the Table Function is meant to return a complete recordset for every call, and a "Join" wants one record set at a time.
What I did, was have the Table Function return ALL the records needed for the entire Select. In other words, Select from Progress_Notes all the required Date_ values, or Min(), Max() range of Date_, placed into @Vars, then use the @Vars as the parameters to the Table Function. The Table Function should include the column with Date_ values so you can use it as part of Join.
I'm hoping like you are, that someone comes back and tells us it can be done in an "inline" manner.
Once you understand the BITs, all the pieces come together
-
Thanks so much for the help. I created a seried of functions
1. Create fn1 that selects the records by the desired date from T1 and add an expression for the starting date range, DT1. The ending date, DT2, is already a field.
2. Create fn2 the uses the fn1 joined with the T2 table by the ContactID and
where the date in T2 is >=T1.D1 and <= T1.D2
The parameter for fn1 is the same passed to fn2. fn2 also insludes the NoteID field from fn1.
3. Create fn3 with will use fn2 to summarize the data, giving sums and counts. It is grouped by ContactID and NoteID. Again, the same parameter passed to fn3 is used for fn2
4. Create a final fn4 that takes T1 and joins it to fn3 by the Contact and NoteID to get the summary data for each note. The same parameter is passed through all 4 functions.
This nesting of the functions get the desired results which is a parameterized view with summary data from the joined table.
Again, thanks for pointing me in the right direction to figure this out.
Cammy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply