USE [SqlserverDBName]
GO
/***********************************************************
*DataServer: XXX.XXX.XXX.XXX
*DataBase: SqlserverDBName
*Name: UP_YourSqlserverDBName
*Function: UP_YourSqlserverDBName test
*Input:
*Output:
*Creator: greatwqs
*Updated: 2012-08-21 temp table test
***********************************************************/
CREATE PROCEDURE [dbo].[UP_TEST_TEMP_TABLE]
(
@param1 varchar(MAX),
@param2 varchar(2)
)
AS
BEGIN
SET NOCOUNT ON
-- 不显示执行后的控制台信息
-- Create Temp Table 'TAB_XXXXXXXXXXXX'
IF OBJECT_ID(N'tempdb.dbo.#TAB_XXXXXXXXXXXX', N'U') IS NOT NULL
BEGIN
DROP TABLE #TAB_XXXXXXXXXXXX
-- use temp table not reference dbo.tabxxxx, del dbo
END
CREATE TABLE #TAB_XXXXXXXXXXXX(
[TransID] [int] IDENTITY(1,1) NOT NULL,
ItemName char(25) NOT NULL,
CONSTRAINT [PK_TAB_XXXXXXXXXXXX] PRIMARY KEY CLUSTERED
(
[TransID] ASC
) ON [PRIMARY]
)ON [PRIMARY]
-- INSERT DATA TO TEMP TABLE
WHILE(@param1 = 'ABCDEFG')
BEGIN
INSERT INTO #TAB_XXXXXXXXXXXX(ItemName)
VALUES ('ITEM_NAME')
END
SELECT ItemName FROM dbo.#TAB_XXXXXXXXXXXX
-- 临时表可以不使用WITH(NOLOCK)
DROP TABLE #TAB_XXXXXXXXXXXX
END
GO
在SQL SERVER存储过程中, 如果数据较少200条, 也可以使用表变量:
BEGIN
-- 1. 声明
DECLARE @V_Repeated TABLE(ProductID INT)
-- 2. 插入数据
INSERT INTO @V_Repeated(ProductID)
SELECT TOP (50) ProductID
FROM DBO.Products WITH(NOLOCK)
-- 3. 使用表变量数据
UODATE DBO.XXXXXXX
SET XXXX='VALUE1'
WHERE PRODUCT_FK IN (
SELECT TOP 50 ProductID
FROM @V_Repeated
)
END