Query - Need to build a stored procedure

Question

Post reply

Query - Need to build a stored procedure

rizwan_usa

Old Hand

Points: 334
More actions
October 26, 2007 at 5:03 am

#183287

Hi,

Lets get this too.....

In AdventureWorks there are two Tables- Sales.CurrencyRate, Sales.Currency

Logic::

I need to use them to create a stored procedure that would accept 3 Input and give 1 Output

Inputs are : 1. Currency from (Currency Code)

2. Amount

3. Currency to ( Currency Code)

The output would be ConvertedValue

Simplification::

If I pass to the stored procedure GBP, 100, USD --- it should convert the 100 GBP to USD and print it through ConvertedValue Output variable.

Any help would be really appreciated .

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

Was this your homework? If so, you are likely wasting your money.

without error handling:



CREATE PROC conv

    @fromCurrency CHAR(3)

  , @amount MONEY

  , @toCurrency CHAR(3)

  , @result MONEY OUTPUT

AS

    BEGIN

        DECLARE @rate FLOAT

        IF @fromCurrency = 'USD'

            SELECT TOP 1

                    @rate = EndOfDayRate

            FROM    Sales.CurrencyRate cr1

            WHERE   cr1.FromCurrencyCode = 'USD'

                    AND cr1.ToCurrencyCode = @toCurrency

            ORDER BY cr1.CurrencyRateDate DESC

        ELSE

            SELECT TOP 1

                    @rate = 1 / EndOfDayRate

            FROM    Sales.CurrencyRate cr1

            WHERE   cr1.FromCurrencyCode = 'USD'

                    AND cr1.ToCurrencyCode = @fromCurrency

            ORDER BY cr1.CurrencyRateDate DESC

        SELECT  @result = @amount * @rate

    END

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

VaiydeyanathanVS

Hall of Fame

Points: 3053

October 26, 2007 at 6:11 am

#745666

I guess Andras is Right. This SP should work for you against the spec given by your employer/client.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

KISS principle :



CREATE PROC spc_Currencyconvertor

 @FromCurrencyCode CHAR(3),

 @ToCurrencyCode CHAR(3),

 @Amount MONEY ,

 @CurrencyRateDate DATETIME ,

 @ConvertedValue MONEY OUTPUT 

AS

BEGIN

SET NOCOUNT ON

IF @CurrencyRateDate IS NULL

  BEGIN

	SET @CurrencyRateDate = getdate()

  END

-- remove timepart from datetime

SELECT @CurrencyRateDate = DATEADD(d, 0, DATEDIFF(d, 0, @CurrencyRateDate))

SELECT @ConvertedValue = @Amount * CR.EndOfDayRate

FROM Sales.Currency CF

INNER JOIN Sales.CurrencyRate CR

	ON CR.[FromCurrencyCode] = CF.[CurrencyCode]

	AND CF.[CurrencyCode] = @FromCurrencyCode

	AND CR.[CurrencyRateDate] = @CurrencyRateDate

INNER JOIN Sales.Currency CT

	ON CR.[ToCurrencyCode] = CT.[CurrencyCode]

	AND CT.[CurrencyCode] = @ToCurrencyCode 

END

execute example:



DECLARE @rc int

DECLARE @FromCurrencyCode char(3)

DECLARE @ToCurrencyCode char(3)

DECLARE @Amount money

DECLARE @CurrencyRateDate datetime

DECLARE @ConvertedValue money

-- TODO: Set parameter values here.

EXECUTE @rc = [AdventureWorks].[dbo].[spc_Currencyconvertor]

   @FromCurrencyCode

  ,@ToCurrencyCode

  ,@Amount

  ,@CurrencyRateDate

  ,@ConvertedValue OUTPUT

PRINT @ConvertedValue