Introduction
I personally
do not like the system of nesting Temporary table within Different Stored
procedure. But lot of developer prefers it. Here in this article we are going
to demonstrate it.
Nested Stored Procedure With
Temporary Table
First we
understand the structure.
Hope you
understand it by the above pictorial diagram. The above diagram is in correct
format.
Here I am showing another diagram bellow
The second diagram
represents showing the Wrong implementation. So the Temporary table must
present in the main calling stored procedure, the first pictorial diagram
represent that.
Now make some
Practical Work
The correct format:
-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
CREATE TABLE #T1
(
IDNO INT,
EMPNAME VARCHAR(50)
);
EXEC [dbo].[Procedure2];
EXEC [dbo].[Procedure3];
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
INSERT INTO #T1
(IDNO, EMPNAME)
VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
SELECT * FROM #T1;
END
GO
-- Executing
EXEC [dbo].[Procedure1];
Output:
IDNO
EMPNAME
-----------
--------------------------------------------------
1
Joydeep Das
2
Deepasree Das
Now the Wrong Format:
-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
EXEC [dbo].[Procedure2];
EXEC [dbo].[Procedure3];
EXEC [dbo].[Procedure4];
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
CREATE TABLE #T1
(
IDNO INT,
EMPNAME VARCHAR(50)
);
END
GO
-- 3
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
INSERT INTO #T1
(IDNO, EMPNAME)
VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
END
GO
-- 4
CREATE PROCEDURE [dbo].[Procedure4]
AS
BEGIN
SELECT * FROM #T1;
END
GO
-- Executing
EXEC [dbo].[Procedure1];
Output:
Msg 208, Level 16,
State 0, Procedure Procedure3, Line 6
Invalid object name
'#T1'.
Msg 208, Level 16,
State 0, Procedure Procedure4, Line 6
Invalid object name
'#T1'.
Hope you like
it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment