Pass atemp table
- Would it be possible to a pass a # temp table created and populated in one sp to another sp - to be futher manipluated?????If so - how do I do itThanks
-
Adam,
you need to create a global temp table using ## instead of #
Markus
[font="Verdana"]Markus Bohse[/font]
-
Not really... if you create a # temp table (non-global) in a proc, that temp table will be available in any proc below that because it's the same session (although it's a questionable practice).
Here's the proof...
CREATE PROC dbo.OneLevelDown AS
SELECT * FROM #SomeTempTable
GO
CREATE PROC dbo.Main AS
CREATE TABLE #SomeTempTable (SomeInt INT, SomeString VARCHAR(20))
INSERT INTO #SomeTempTable (SomeInt,SomeString)
SELECT 1,'A' UNION ALL
SELECT 2,'B'
EXEC dbo.OneLevelDown
GO
EXEC dbo.Main
Of course, dynamic SQL has a slightly different set of requirements...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the replies - if I go for ## does it still allow users to run the sps at the same time and create separate tables - and these can be refenced by follow up sps??
-
No, if you do a ## it will not allow multiple users all the users will be dumping records into the same temp table.
Populate your temp table inside a procedure.
Create Procedure MyStoredProcedure
as
Select Foo, Bar
From MyTable
GO
Create Procedure myproc
as
create #temptable (Val1 int, val2 int)
-- Call procedure where the results will populate your temp table
Insert into #TempTable (Val1, Val2)
exec MyStoredProcedure
-- Do stuff
select val1, val2
from #Temptable
GO
- I think Ive got it -I have results from one sp used to populate the temp table in another sp - both of which use temporary tables.Is Ray and Jeffs examples basically the same???Thanks
-
Yep...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
They have given you the correct answer. I have always wondered why you cannot use a table variable as a parameter to a stored procedure. This would make these scenarios much simpler.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I was pleasantly surprised to find that this was the case in an application I wrote, but I'm curious why it's a questionable practice. Is there another way to achieve the same effect?
Thanks,
Mattie
-
I don't know of another way to achieve the same thing, except for putting all your code in one stored procedure. I have used this several times. If, as I mentioned in my earlier post, there was a way to pass a table variable to an sp then you could eliminate the use of temp tables.
I suppose you could use SQL Server's builtin XML capabilities to pass data around, but I don't think it would be effecient.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
You can pass the table name as an argument to the lower SP.
In the calling SP -
create table dbo.#temp (resultField int)
exec dbo.calledSP @dataValue, 'dbo.#temp'
select * from dbo.#temp
The called procedure -
CREATE PROCEDURE dbo.calledSP @dataValue varchar(200), @tempTable varchar(80) ='#none#' AS
-- lots of could go here
if @tempTable='#none#' begin
select resultField from resultTable where criteria like @dataValue+'%' -- example. Call with no table name to debug.
end else begin
declare @finalCommand varchar(2000)
set @finalCommand='insert into ' + @tempTable +' (resultField) select resultField from resultTable where criteria like @dataValue+''%'''
exec (@finalCommand)
end
-
What makes it questionable (in my feeble mind) is the idea of code reuse... those other stored procedures rely on the temp table being there when they start... you can't use those sprocs directly... they simply will not run without the temp table being there.
Now, flipping it around, if the code is reused by the same main proc many times, that might be justification for this practice... provided that what's causing you to call the same proc multiple times from the main isn't also a questionable practice (some form of RBAR, usually)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
One method to allow you to re-use the stored procedure which is relying on the temp table is to check for the existence of the temp table at the top of the procedure using Object_Id(temptable) and create it if it does not exist.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Heck... I know that... but now you have code to maintain in two places if the requirement for the schema of the temp table changes. Yeah, I know... it's a risk that must sometimes be taken... but it's not always the right answer and is frequently a bad answer, which is why I called it a questionable practice to begin with. And remember... I knew how to do it which means I agree that it's sometime justifyable... but not often. Perhaps about the same frequency as when a cursor should actually be justified... very rarely.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is what passing a table as a parameter really means:
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply