SQL - Help me Select the result of concatenating two variables in a stored procedure

Question

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 and here is the desired result

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!

Answer 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