CREATE PROCEDURE SQL Server

Question

我试图通过指定输入参数来将不同的变量输入到一个过程中--对不起，如果这很容易的话--我已经使用SAS多年了，并试图弄清楚SQL！

@VAR是我的输入参数。它可以是我正在查看的数据中的数字或文本。我希望能够有10列，我运行这段代码，并通过更改输入分别输出结果-这是可能的吗？

declare @VAR as int???
set @VAR = 'exp_year', 'BusYrsExp'

CREATE PROCEDURE getExpSum @VAR int as
select @VAR, sum(NetCost) as clmCost, sum(claimCount) as clmnum, sum(earneddays) as exposure
into ow_exp_year
from CF_BI
group by @VAR
order by @VAR

好的--如果我需要使用动态sql --这个过程可以只用于一个变量吗？动态sql会使这变得更加困难吗？

Answer 1

如果事先知道@VAR的可能值，就可以编写一条CASE语句。例如，如果您有一个表示某个枚举的int值：

CREATE PROCEDURE getExpSum
    @groupColumn int --enumeration: 1=foo, 2=bar, 3=baz
AS
    SELECT grp, sum(NetCost) as clmCost, 
        sum(claimCount) as clmnum, sum(earneddays) as exposure
    INTO ow_exp_year
    FROM
    (
        SELECT CASE @groupColumn
            WHEN 1 THEN foo
            WHEN 2 THEN bar
            WHEN 3 THEN baz
            END AS grp,
            NetCost, claimCount, earneddays
        FROM CF_BI
    ) x
    GROUP BY grp
    ORDER BY grp