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