CODEWITHSUNDEEP
sql-servert-sql
Carlos80
Carlos80

Reputation: 433

Passing SET variable to Stored Procedure

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

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:

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

Upvotes: 0

Views: 51

Answers (2)

Singh Kailash
Singh Kailash

Reputation: 674

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

EXEC Exchange @CurConv

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

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:

EXEC('

DECLARE @CurConv float

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

') END

Upvotes: 4

Related Questions