表变量
1.
USE AdventureWorks2012; GO --1 DECLARE @IDTable TABLE (ID INT); DECLARE @IDList VARCHAR(2000); DECLARE @ID INT; DECLARE @Loc INT; --2 SET @IDList = '16496,12506,11390,10798,2191,11235,10879,15040,3086'; --3 SET @Loc = CHARINDEX(',',@IDList); PRINT @Loc --4 WHILE @Loc > 0 BEGIN --4.1 SET @ID = LEFT(@IDList,@Loc-1); --4.2 SET @IDList = SUBSTRING(@IDList,@Loc +1,2000); --4.3 INSERT INTO @IDTable(ID) VALUES (@ID); --4.4 SET @Loc = CHARINDEX(',',@IDList); END; --5 IF LEN(@IDList) > 0 BEGIN SET @ID = @IDList; INSERT INTO @IDTable(ID) VALUES (@ID); END; --6 SELECT BusinessEntityID, FirstName, LastName FROM Person.Person AS p INNER JOIN @IDTable ON p.BusinessEntityID = ID;
2。类似于数组的用法:
USE AdventureWorks2012; GO --1 SET NOCOUNT ON; GO --2 DECLARE @IDTable TABLE ( ArrayIndex INT NOT NULL IDENTITY, ID INT ); DECLARE @RowCount INT; DECLARE @ID INT; DECLARE @Count INT = 1; --3 INSERT INTO @IDTable(ID) VALUES(500),(333),(200),(999); --4 SELECT @RowCount = COUNT(*) FROM @IDTable; --5 WHILE @Count <= @RowCount BEGIN --5.1 SELECT @ID = ID FROM @IDTable WHERE ArrayIndex = @Count; --5.2 PRINT CAST(@COUNT AS VARCHAR) + ': ' + CAST(@ID AS VARCHAR); --5.3 SET @Count += 1; END;