2

I am trying to concatenate two variables in a stored procedure in order to use the resulting string in a SELECT statement. The concatenation works as desired, but upon execution the table does not fill correctly. Here's the code:

Create Procedure CurrencyParameter
    @Trade NVARCHAR (5),
    @Target NVARCHAR (5)
AS
    SELECT  
        CONCAT(@Trade, '_', SUBSTRING(@Target, CHARINDEX('U', @Target) + 1, LEN(@Target)))
    FROM 
        Trade_Ratios


EXECUTE CurrencyParameter @Trade = 'CU1', @Target ='CU10';

Here is the result enter image description here and here is the desired result enter image description here

As you can see, the table fills with the correct amount of rows, but simply replaces the data with the concatenated string. Thanks ahead of time!

Guruprakash Chinnasamy
  • 144
  • 10
Michael WU
  • 23
  • 3
  • Give us some sample data from Trade_Ratios table. – ahmed abdelqader Nov 18 '16 at 04:50
  • You can go through the post :- [http://stackoverflow.com/questions/8176485/specifying-column-name-as-a-parameter-in-select-statement](http://stackoverflow.com/questions/8176485/specifying-column-name-as-a-parameter-in-select-statement) – Webdev Nov 18 '16 at 04:51
  • 1
    Another example at this post: http://stackoverflow.com/questions/985842/mysql-concat-to-create-column-names-to-be-used-in-a-query – Nick Falco Nov 18 '16 at 04:54

1 Answers1

1

You need dynamic sql 

CREATE PROCEDURE Currencyparameter (@Trade  NVARCHAR (5),
                                    @Target NVARCHAR (5))
AS
  BEGIN
      DECLARE @sql VARCHAR(8000)= ''

      SET @sql = Concat('SELECT ', @Trade, '_', Substring(@Target, Charindex( 'U', @Target) + 1, Len(@Target)), ' FROM Trade_Ratios')

      EXEC (@sql)
  END
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172