July 23, 2008 at 1:11 pm
Hi,
can a temperory table be passed to stored procedure ? If so how?
July 23, 2008 at 1:24 pm
You cannot pass a temporary table to a stored procedure. You can create a temporary table in stored procedure 1 and then manipulate the data in the temporary table in another stored procedure that is called within stored procedure 1. For example:
[font="Courier New"]ALTER PROCEDURE temp_table_test_1
AS
CREATE TABLE #test
(
test_id INT IDENTITY(1,1),
test NVARCHAR(50)
)
INSERT INTO #test
(
test
)
SELECT
name
FROM
production.product
SELECT * FROM #test
EXEC temp_table_test_2
SELECT * FROM #test
RETURN
GO
ALTER PROCEDURE temp_table_test_2
AS
IF OBJECT_ID('tempdb..#test') IS NOT NULL
BEGIN
UPDATE #test
SET test = 'Jack Corbett'
WHERE
test_id = 1
END
ELSE
BEGIN
SELECT 'Temp table #test does not exist'
END
RETURN
[/font]
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
July 23, 2008 at 1:28 pm
You can create global temp tables (##name) as well, but be careful as these are visible to all connections and you could end up with naming conflicts.
Perhaps you could explain what you want to accomplish and we can see if it makes sense.
July 24, 2008 at 11:22 am
Thank you jack and steve,
I am doing somethin like this in a trigger for update
select * into #td from deleted
select * into #ti from inserted
exec samplestoredproc
This trigger calls a stored procedure, samplestoredproc. And I am trying to access the temperory tables #td and #ti but they are not populated 🙁
Any help is appreciated.
Thanks for your time guys.
July 24, 2008 at 11:45 am
What are you trying to do in the stored proc? Depending on what you are doing, this may not be the best way to go about it. When firing triggers, you want the work being done to be done quickly.
😎
July 24, 2008 at 12:49 pm
Hi Lynn,
I am trying to create a generic audit process in stored proc. I know there is going to be some performance degradation. But my manager wants it this way though performance will be affected :(. So trying to access these temp tables in stored proc but i donot find these temp tables being populated.
Thanks a lot for the reply.
July 24, 2008 at 1:41 pm
Bah! Mangers!
The code you are writing should work. I created the following in AdventureWorks and it works fine:
Trigger
[font="Courier New"]
CREATE TRIGGER HumanResources.Department_Upd
ON HumanResources.Department
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
PRINT 'In trigger'
SELECT
'inserted' AS table_name,
*
INTO
#inserted
FROM
inserted
SELECT
'deleted' AS table_name,
*
INTO
#deleted
FROM
deleted
EXEC test_trigger
END[/font]
Stored Procedure:
[font="Courier New"]CREATE PROCEDURE test_trigger
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM #inserted
SELECT * FROM #deleted
END
GO
[/font]
Update statement:
[font="Courier New"]UPDATE HumanResources.Department
SET Name = 'Engineering Test'
WHERE
DepartmentID = 1[/font]
Results:
In trigger
table_name DepartmentID Name GroupName ModifiedDate
---------- ------------ -------------------------------------------- -------------------------------------------- -----------------------
inserted 1 Engineering Test Research and Development 2008-07-24 15:38:57.037
table_name DepartmentID Name GroupName ModifiedDate
---------- ------------ ------------------------------------------- -------------------------------------------------- -----------------------
deleted 1 Engineering Research and Development 2008-07-24 15:38:57.037
In trigger
table_name DepartmentID Name GroupName ModifiedDate
---------- ------------ -------------------------------------------- -------------------------------------------- -----------------------
inserted 1 Engineering Test Research and Development 2008-07-24 15:39:03.897
table_name DepartmentID Name GroupName ModifiedDate
---------- ------------ -------------------------------------------- -------------------------------------------------- -----------------------
deleted 1 Engineering Test Research and Development 2008-07-24 15:38:57.037
The second pass is because I happened to hit a table that has another update trigger on it.
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
July 25, 2008 at 9:28 am
Wow.. Jack, U are awesome. That was quite an effort on ur part. Thank you very much 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply