SqlServer 临时表、表变量、函数 替代游标

 

http://www.cnblogs.com/chongzi/archive/2011/01/19/1939106.html

 

临时表

存放在tempdb中

--存储过程中将多表连接结果写入到临时表中，然后通过游标查询临时表内容  
--判断临时表是否存在
IF    OBJECT_ID('tempdb..#TmpTable') IS NOT NULL
DROP TABLE #TmpTable

SELECT a.[a1],a.[a1],b.[b1],b.[b2]
INTO #TmpTable
FROM A a
LEFT JOIN B b ON a.a1 = b.a1

 

操作游标

DECLARE @orderId NVARCHAR(50),@customerId NVARCHAR(50),@employeeId NVARCHAR(50)
DECLARE myCursor CURSOR FOR
SELECT o.OrderID,o.CustomerID,o.EmployeeID FROM Orders AS o

OPEN myCursor
fetch next from myCursor into @orderId,@customerId,@employeeId
while @@fetch_status=0 
BEGIN

PRINT @orderId
PRINT @customerId
PRINT @employeeId
fetch next from myCursor into @orderId,@customerId,@employeeId
END
close myCursor 
deallocate myCursor