T-Sql 实现类似访问数组变量的操作

      目前T-SQL不支持Array这种类型，大多数情况我们需要用游标来实现。除了游标后我们还可以用临时表，这里我们演示使用表变量来实现，直接看下来的T-SQL：

 

--Then "iterate" through it executing the necessary SQL from those values.
--This will allow you to have 0 to MANY values to be executed, so you don't have to set up a variable for each.
--The following is a complete sample of how you may go about doing that without cursors.

SET NOCOUNT ON
DECLARE @dict TABLE (
                    id          INT IDENTITY(1,1), -- a unique identity column for reference later
                    value       VARCHAR(50),       -- your parameter value to be passed into the procedure
                    executed    BIT                -- BIT to mark a record as being executed later
                    )

-- INSERT YOUR VALUES INTO @dict HERE
-- Set executed to 0 (so that the executio    n process will pick it up later)
-- This may be a SELECT statement into another table in your database to load the values into @dict
INSERT @dict
SELECT '390adbc5-3494-4651-95af-608b69a304c1',0  UNION ALL
SELECT 'ddf23828-fbf9-4d16-81fa-3f4b465539a3',0     UNION ALL
SELECT '02627340-22cd-4758-807d-6251acd5a0e5',0

DECLARE @currentid INT
DECLARE @currentvalue VARCHAR(50)
WHILE EXISTS(SELECT * FROM @dict WHERE executed = 0)
BEGIN
    -- Get the next record to execute
    SELECT
    TOP 1   @currentid = id
    FROM    @dict
    WHERE   executed = 0

    -- Get the parameter value
    SELECT  @currentvalue = value
    FROM    @dict
    WHERE   id = @currentid

    -- EXECUTE THE SQL HERE
    ---BEGIN
    PRINT   'SecondSP ' +  '@myParam int ' + '@myParam = ' + @currentvalue
    -- END

    -- Mark record as having been executed
    UPDATE  d
    SET     executed = 1
    FROM    @dict d
    WHERE   id = @currentid

END

 

上面的T-SQL先创建一个表变量，然后插入初始数据，这些数据可能是你需要处理的数据。然后有一个设有一个标志列标记当前行有没有执行过。你可以根据上面的代码模板修改成为你自己的。

希望对您开发有帮助。

您可以感兴趣的文章：

T-SQL使用DbMail发送多个附件

在T-SQL中使用正则表达式函数

SQLSERVER2008使用CTE转换string到Table

SqlServer中使用T-sql找出identity列

 

作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。