sql-server - 使用参数添加列？

Question

我有一个存储过程，可以将货币记录添加到我的汇率表中，但我还需要一个用于添加货币的相应列。

表结构如下:

CurrencyID  CurrencyName Rupee                  Euro                   Dollar                 Pound
----------- ------------ ---------------------- ---------------------- ---------------------- ----------------------
1           Rupee        1                      0.008                  0.009                  0.007
2           Euro         121.3                  1                      1.08                   0.84
3           Dollar       111.4                  0.91                   1                      0.77
4           Pound        143.6                  1.18                   1.28                   1

到目前为止我的存储过程是这样的:

CREATE PROCEDURE addCurrency 
    @CurrencyName varchar(30),
    @Rupee float,
    @Euro float,
    @Dollar float,
    @Pound float
AS
BEGIN
    INSERT into [dbo].[CurrencyTbl] (CurrencyName, Rupee, Euro, Dollar, Pound) 
    VALUES (@CurrencyName, @Rupee, @Euro, @Dollar, @Pound)
END
BEGIN
    DECLARE @SQL VARCHAR(1000)

    SELECT @SQL = 'ALTER TABLE [dbo].[CurrencyTbl] ADD ' + @CurrencyName + ' VARCHAR(30)'
END

但是没有创建列

Answer 1

并不是说我认为这是个好主意，但实际上您并没有执行创建的 @SQL，您只是选择了它。

您的代码容易受到 SQL 注入(inject)攻击(因为您直接使用连接参数执行 sql)，所以要小心该代码。

此外，如果您要存储数字，为什么您的数据类型是 varchar(30)？您的其他数据类型是 float(可能应该是 numeric/decimal 而不是 float)。

你可以像这样使用exec sp_executesql @SQL:

CREATE PROC addCurrency @CurrencyName  varchar(30),@Rupee float,
@Euro float,@Dollar float,@Pound float
    AS
    BEGIN
    INSERT into [dbo].[CurrencyTbl] (CurrencyName , Rupee,Euro, Dollar,Pound ) 
    VALUES (@CurrencyName,@Rupee,@Euro,@Dollar,@Pound )
    END
    BEGIN
    Declare @SQL nVarChar(1000)
    SELECT @SQL = 'ALTER TABLE [dbo].[CurrencyTbl] ADD ' + @CurrencyName + ' float;'
    exec sp_executesql @SQL;
    END

动态数据库

• The curse and blessings of dynamic SQL - Erland Sommarskog
• sp_executesql

---

例如:

/* Monies is the term used by Irkens to refer to their form of currency */
exec addCurrency 'Monies',1,1,1,1 
select * from CurrencyTbl

rextester 演示:http://rextester.com/CUC99912

返回:

+------------+--------------+------------+----------+----------+----------+--------+
| CurrencyID | CurrencyName |   Rupee    |   Euro   |  Dollar  |  Pound   | Monies |
+------------+--------------+------------+----------+----------+----------+--------+
|          1 | Rupee        | 1,000000   | 0,008000 | 0,009000 | 0,007000 | NULL   |
|          2 | Euro         | 121,300000 | 1,000000 | 1,080000 | 0,840000 | NULL   |
|          3 | Dollar       | 111,400000 | 0,910000 | 1,000000 | 0,770000 | NULL   |
|          4 | Pound        | 143,600000 | 1,180000 | 1,280000 | 1,000000 | NULL   |
|          5 | Monies       | 1,000000   | 1,000000 | 1,000000 | 1,000000 | NULL   |
+------------+--------------+------------+----------+----------+----------+--------+

---

最好为您的表考虑一种不需要使用动态 sql 添加新列和更新列的替代形式。

这是一个选项:

create table CurrencyTbl (FromCurrencyName varchar(30), ExchangeRate decimal(19,6), ToCurrencyName varchar(30))
insert into CurrencyTbl values
 ('Rupee ',1.000000,'Rupee')
,('Rupee ',0.008000,'Euro')
,('Rupee ',0.009000,'Dollar')
,('Rupee ',0.007000,'Pound')
,('Euro  ',121.300000,'Rupee')
,('Euro  ',1.000000,'Euro')
,('Euro  ',1.090000,'Dollar')
,('Euro  ',0.850000,'Pound')
,('Dollar',111.400000,'Rupee')
,('Dollar',0.910000,'Euro')
,('Dollar',1.000000,'Dollar')
,('Dollar',0.770000,'Pound')
,('Pound ',143.600000,'Rupee')
,('Pound ',1.180000,'Euro')
,('Pound ',1.280000,'Dollar')
,('Pound ',1.000000,'Pound')

你可以像这样动态地旋转表格:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + quotename(ToCurrencyName)
      from CurrencyTbl
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
select  @sql = '
 select FromCurrencyName as CurrencyName,' + @cols + '
  from CurrencyTbl
 pivot (max([ExchangeRate]) for [ToCurrencyName] in (' + @cols + ') ) p'
exec sp_executesql @sql;

rextester 演示:http://rextester.com/EQSC62833

返回:

+--------------+----------+----------+----------+------------+
| CurrencyName |  Dollar  |   Euro   |  Pound   |   Rupee    |
+--------------+----------+----------+----------+------------+
| Dollar       | 1,000000 | 0,910000 | 0,770000 | 111,400000 |
| Euro         | 1,090000 | 1,000000 | 0,850000 | 121,300000 |
| Pound        | 1,280000 | 1,180000 | 1,000000 | 143,600000 |
| Rupee        | 0,009000 | 0,008000 | 0,007000 | 1,000000   |
+--------------+----------+----------+----------+------------+