SQL Dynamic Exchange Rate-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

Aside from the design questions (see comments) your dynamic code tries to execute the following query:

SELECT RATE 
FROM USERCONFIG.dbo.curr 
WHERE CODE = GBP

So it tries to match column CODE to column GBP. Hence the error.

The way to build dynamic SQL with string conversions is to use single quotes twice around the string like:

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

The repeated single quotes are turned into a single quote on execution.