SQL Dynamic Exchange Rate

Question

I'm trying to create a stored procedure that provided the exchange rate based on the users selection.

When I simply type this query, it works fine:

SELECT RATE 

FROM USERCONFIG.dbo.curr 

WHERE CODE = 'GBP'

However when I try to transfer this into a stored procedure:

ALTER PROCEDURE [dbo].[TEST] (@CCY char(3))

AS
BEGIN

    SET NOCOUNT ON;

EXEC('

SELECT RATE 

FROM USERCONFIG.dbo.curr 

WHERE CODE = ' + @CCY + '
    
    ')
    
END

I get the following error message and I can't work out why.

Answer 1

It's unclear why you are using dynamic SQL here, perhaps you should flesh out your example.

But what you need to do is parameterize it properly, using sp_executesql. Do not inject it like in the other answer.

ALTER PROCEDURE [dbo].[TEST] (@CCY char(3))

AS

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'
SELECT RATE 
FROM USERCONFIG.dbo.curr 
WHERE CODE = @CCY;
';

EXEC sp_executesql
  @sql,
  N'@CCY char(3)',
  @CCY = @CCY;

Answer 2

You don't need Dynamic SQL at all here, the reason you are getting the error is because you are. As you have confirmed that the statement you have is literally all you have (there isn't a hidden requirement) just use your non-dynamic statement in your procedure:

ALTER PROCEDURE [dbo].[TEST] (@CCY char(3)) AS
BEGIN

    SET NOCOUNT ON;

    SELECT RATE 
    FROM USERCONFIG.dbo.curr 
    WHERE CODE = @CCY;
    
END;