如何在SQL Server中实现动态查询的视图等效效果？能否用存储过程/函数？

我有如下SQL代码：

DECLARE @i INT = 1;
DECLARE @sql_code varchar(max) = '';
DECLARE @repeats INT = 4;

WHILE @i <= @repeats
BEGIN
    SET @sql_code = @sql_code+'SELECT ''foo'+cast(@i as varchar)+''' as bar UNION ALL '
    
    SET @i = @i + 1
END;

SET @sql_code = LEFT(@sql_code,LEN(@sql_code) - 10)

exec (@sql_code)

在SSMS中运行这段代码后，得到如下结果：

bar
----
foo1
foo2
foo3
foo4

我想通过视图动态复现上述结果，该如何操作？我知道视图中无法使用变量声明，那能否通过存储过程或函数实现？

视图确实不支持变量、循环这类动态逻辑，以下是几种可行的替代实现方式：

1. 表值函数（推荐，用法接近视图） #

可以创建一个内联表值函数，既能接受参数控制重复次数，又能像视图一样被查询：

CREATE FUNCTION dbo.GetFooValues(@repeats INT)
RETURNS TABLE
AS
RETURN (
    SELECT 'foo' + CAST(n AS VARCHAR) AS bar
    FROM (
        -- 利用系统表生成连续数字，也可改用其他数字生成方式
        SELECT TOP (@repeats) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_columns
    ) t
)

调用示例：

-- 获取4条结果
SELECT * FROM dbo.GetFooValues(4);

如果需要固定返回4条，可把参数去掉，直接写TOP(4)，这样用法就和普通视图完全一致。

2. 存储过程 #

如果需要保留原代码的动态拼接逻辑，可以用存储过程实现：

CREATE PROCEDURE dbo.GenerateFooValues
    @repeats INT = 4 -- 默认返回4条
AS
BEGIN
    DECLARE @sql_code VARCHAR(MAX) = '';
    DECLARE @i INT = 1;

    WHILE @i <= @repeats
    BEGIN
        SET @sql_code = @sql_code + 'SELECT ''foo' + CAST(@i AS VARCHAR) + ''' as bar UNION ALL ';
        SET @i = @i + 1;
    END;

    -- 去掉最后多余的UNION ALL
    SET @sql_code = LEFT(@sql_code, LEN(@sql_code) - 10);
    EXEC (@sql_code);
END;

调用示例：

-- 执行存储过程获取结果
EXEC dbo.GenerateFooValues 4;

存储过程适合直接执行返回结果，但无法像视图/函数那样嵌套在其他SELECT语句中使用。

3. 递归CTE（无需创建对象） #

如果只是临时生成结果，不想创建函数或存储过程，可以用递归CTE实现：

WITH FooCTE AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM FooCTE WHERE n < 4 -- 4为重复次数
)
SELECT 'foo' + CAST(n AS VARCHAR) AS bar FROM FooCTE;

这种方式逻辑直观，不需要依赖系统表，修改数字就能调整结果行数。

内容的提问来源于stack exchange，提问作者Monardo