将SET变量传递给存储过程

I think this is pretty straight forward, however my experience with SP's is quite limited.

我认为这很简单，但我对SP的经验非常有限。

I have a fairly lengthy query that I'm now trying to pass into a SP, I haven't had much luck so I'm breaking it down into sections and I'm stuck on the following part:

我有一个相当冗长的查询，我现在正试图传递给SP，我没有太多的运气，所以我把它分解成各个部分，我被困在以下部分：

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

EXEC('

DECLARE @CurConv float

SET @CurConv = (Select XRATE FROM CURRENCY.dbo.currfx where CODE = ' + @CCY + ')

') END

The error I keep getting is:

我一直得到的错误是：

Msg 207, Level 16, State 1, Line 5
Invalid column name 'GBP'.

The problem is GBP is not a column its a value? Any help would be much appreciated.

问题是英镑不是一个列的值吗？任何帮助将非常感激。

Thanks

谢谢

2 个解决方案

#1

Your dynamic query being executed is

正在执行的动态查询是

SET @CurConv = (Select XRATE FROM CURRENCY.dbo.currfx where CODE = GBP

But is should be

但应该是

SET @CurConv = (Select XRATE FROM CURRENCY.dbo.currfx where CODE = 'GBP'

So you have to surround value of @CCY with additional single quotes:

所以你必须使用额外的单引号来包围@CCY的值：

EXEC('

DECLARE @CurConv float

SET @CurConv = (Select XRATE FROM CURRENCY.dbo.currfx where CODE = ''' + @CCY + ''')

') END

#2

DECLARE @CurConv float Select @CurConv = XRATE FROM CURRENCY.dbo.currfx where CODE = @CCY

DECLARE @CurConv float选择@CurConv = XRATE FROM CURRENCY.dbo.currfx，其中CODE = @CCY

EXEC Exchange @CurConv

#1