USE SHP_TEST
GO

-- CREATE A STORED PROCEDURE TO CREATE A TEMPORARY TABLE
IF OBJECT_ID('SP_TEMP_TABLE_CREATE','P') IS NOT NULL
DROP PROCEDURE SP_TEMP_TABLE_CREATE
GO

CREATE PROCEDURE SP_TEMP_TABLE_CREATE
--  ADD PARAMETERS HERE
 @PACKAGENAME VARCHAR(30)
AS
 -- CREATE A TEMPORARY TABLE
 SELECT NAME,AGE INTO
  #TEMP_USER
 FROM
  (
   SELECT 'SIMON' AS NAME,20 AS AGE UNION
   SELECT 'JOHN',30 UNION
   SELECT 'BEN',20
  )TEST_TABLE
 
 --HERE TO ADD STORED PROCEDURES WHICH WILL CALL THE TEMPORARY TABLES IN THE CURRENT STORED PROCEDURE
 EXEC SP_CALL_TEMP_TABLE_IN_SP @PACKAGENAME
GO

 

-- CREATE ANOTHER STORED PROCEDURE TO CALL THE TEMPORARY TABLE
IF OBJECT_ID('SP_CALL_TEMP_TABLE_IN_SP','P') IS NOT NULL
DROP PROCEDURE SP_CALL_TEMP_TABLE_IN_SP
GO

CREATE PROCEDURE SP_CALL_TEMP_TABLE_IN_SP
 @PACKAGENAME VARCHAR(30)
AS
 SELECT * FROM #TEMP_USER
GO

創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()