Using Parameters to Add Columns?

Question

I have a stored procedure which adds a currency record to my exchange rate table but I also need a corresponding column for the added currency.

The table structure is as follows:

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

and my stored procedure so far is this:

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

But the column is not created

SqlZim · Accepted Answer

Not that I think any of that is a good idea, but you do not actually execute your created @SQL, you are just selecting it.

Your code vulnerable to SQL Injection (because you are directly executing sql with concatenated parameters), so be careful with that code.

Also, if you are storing numbers, why is your datatype varchar(30)? Your other datatypes are float (which should probably be a numeric/decimal instead of float).

You could use exec sp_executesql @SQL like so:

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

dynamic sql

For example:

/* 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 demo: http://rextester.com/CUC99912

returns:

+------------+--------------+------------+----------+----------+----------+--------+
| 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   |
+------------+--------------+------------+----------+----------+----------+--------+

It may be better to consider an alternative form for your table that does not require adding new columns and updating columns using dynamic sql.

Here is one option:

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')

And you could pivot the table dynamically like so:

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 demo: http://rextester.com/EQSC62833

returns:

+--------------+----------+----------+----------+------------+
| 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   |
+--------------+----------+----------+----------+------------+

Using Parameters to Add Columns?

Answers (2)

Related Questions