December 2, 2016 at 8:30 am
Hi,
I have stored procedure with creates a temp table and stores data.Another procedure is called inside the main procedure, which should insert data into same temp table.stored procedure insert into temp table.But when execute the main procedure only data insert into main temp table will display.How to pass data inserted from 2 nd proc to main procedure.
December 2, 2016 at 8:48 am
If a Procedure calls another procedure INSIDE it's code, any #temp tables are available to it that exist in the procedure;
if the procedures are called consecutively, then the temp table has to exist prior to the first procs call, or if the proc will create the table, then only a global temp table(##Temp) will be available in that scope.
so if the proc is called inside
CREATE PROCEDURE Example
AS
BEGIN
-- Create the temp table
CREATE TABLE #temp (tempid int, temptext varchar(30))
--populate it
INSERT INTO #temp
SELECT * FROM [SomeAnonymousTable)
--call a child procedure that might do something witht eh temp table
EXECUTE ExampleProcTwo
END --PROC
GO
EXECUTE Example
CREATE PROCEDURE Example
AS
BEGIN
-- Create the global temp table
CREATE TABLE ##temp (tempid int, temptext varchar(30))
--populate it
INSERT INTO ##temp
SELECT * FROM [SomeAnonymousTable)
END --PROC
GO
EXECUTE Example
EXECUTE ExampleProcTwo
Lowell
December 4, 2016 at 8:03 pm
Is it what you're after?
IF OBJECT_ID('Proc2') is null
exec ('CREATE PROC Proc2 as SELECT 1 as One')
GO
ALTER PROC Proc2
AS
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
SELECT * FROM #TempTable
GO
IF OBJECT_ID('Proc1') is null
exec ('CREATE PROC Proc1 as SELECT 1 as One')
GO
ALTER PROC Proc1
AS
CREATE TABLE #TempTable (
ID INT,
Name VARCHAR(50)
)
INSERT INTO #TempTable ( ID, Name )
SELECT 1, 'Name 1'
UNION
SELECT 2, 'Name 2'
EXEC proc2
GO
EXEC Proc1
_____________
Code for TallyGenerator
December 5, 2016 at 3:51 pm
The problem may be that you are also creating a table inside the inner stored procedure ( with the same name I presume). In these cases each object is local to the procedure you created it in. Have a look at this code :
create procedure #usp_proc2
as
begin
--create table #userTable
--(
--id int,
--someString varchar(30)
--)
insert into #userTable
select 3, 'someString 3'
union all
select 4, 'someString 4'
end
go
-----------------------------------------------
create procedure #usp_proc1
as
begin
create table #userTable
(
idint,
someString varchar(30)
);
insert into #userTable
select 1, 'someString 1'
union all
select 2, 'someString 2'
;
Exec #usp_proc2;
select * from #userTable;
end
go
-----------------
exec #usp_proc1
---------------------
drop procedure #usp_proc1
drop procedure #usp_proc2
This shows you the results that you want as is. Try running it....
Next uncomment the portion that declares a table within procedure 2, the one at the top. Run the whole transaction now and see how you will run into the issue that you describe. With this knowledge I leave this to you to figure how to proceed with the design of your code.
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply