CODEWITHSUNDEEP
sql-serverfunctiont-sqlstored-proceduresset
Jake McBride
Jake McBride

Reputation: 55

SQL: Issue SETing value to SELECT statement value

SHORT VERSION: SET @UnitRate is always being set to the same value no matter what UnitRateID is being passed to the function and stored in @UnitRateID

LONG VERSION: I am having a problem setting @UnitRate to a different value when my function is passed in different @UnitRateID's. It seems that no matter what @UnitRateID is passed in I will always get the UnitRate returned from my SELECT statement that I am SETing @UnitRate to as the value for UnitRateID of 11.

I believe I am passing in a different value correctly because an sproc that I have for creating a reservation will correctly populate my table with different UnitRateID's

Also I should note that if I take the SELECT statement and run it outside of the SETing of @UnitRate and manually set WHERE ur.UnitRateID = @UnitRateID to WHERE ur.UnitRateID = n where n is my UnitRateID I will get different return values. There is a commented out section where I test to see if there is an issue due to my other function that calculates the Lodging Tax Rates and I have found that when I set the @deposit to @UnitRate that the bug is indeed within this function because I get the same deposit value (that of the @UnitRate) no matter the passed in UnitRateID.

Is my SETing syntax here correct? Would there be something wrong with how I am setting it to cause it to always grab and return the value for say, UnitRateID of 11?

I cannot share the specifics of the DB schema itself, but if there is any more information needed to dissect the issue, let me know!

CODE: My function to calculate deposit...

CREATE FUNCTION dbo.CalculateDeposit
(
    @UnitRateID smallint,
    @ResCheckInDate date
)
RETURNS smallmoney
AS
    BEGIN
        DECLARE @deposit smallmoney
        DECLARE @PropertyID smallint
        DECLARE @UnitRate smallmoney
        SET @deposit = 0
        SET @UnitRate = (
            SELECT ur.UnitRate
            FROM UnitRate as ur
            WHERE ur.UnitRateID = @UnitRateID
        )
        SET @PropertyID = (
            SELECT p.PropertyID
            FROM Property as p
            JOIN UnitRate as ur
            ON ur.PropertyID = p.PropertyID
            AND ur.UnitRateID = @UnitRateID
        )
        SET @deposit = ROUND((@UnitRate + (@UnitRate * (0.01 *(SELECT (dbo.GetLodgingTaxRate(@PropertyID, @ResCheckInDate)))))), 2);
        --The below commented out section is for testing
        --SET @deposit = @UnitRate
        RETURN @deposit
    END
GO

EXAMPLE CALL:

SET @deposit = dbo.CalculateDeposit(@UnitRateID, 'YYYY-MM-DD')

I then take that deposit value and pass it into an sproc. Not that the @UnitRateID and @deposit here are not in the function I showed and therefor are not in the same scope. @UnitRateID is actually a hard coded value when I usually call it and @deposit is passed into my sproc.

EDITS:

Requested Property and UnitRate Table Data: http://gyazo.com/3f399914bd889c52e4cc85be97fcc2e6

Issue may not be code??:

I had posted on here assuming, after checking quite a bit of data and other code, that I had some issue with my code and syntax. I isolated it to the posted function based on the commented out SET @deposit = @UnitRate test... it seems the error may not be syntax but rather with my data and or tables themselves?? What do you guys think?

Upvotes: 0

Views: 556

Answers (1)

Marcellinov
Marcellinov

Reputation: 301

The select that returns the value of @UnitRate seems ok.

It is not easy understand why the SET @UnitRate in your function return always the value associated to UnitRateID = 11 with only those provided elements.

Unfortunately, you can't use a PRINT statement inside your Function in TSQL (here the post in stackoverflow) .

You can try instead another approach: modify the function and return the value of @UnitRate and try to understand if the problem is within the function or not.

Here an example (I tried, as much as possible, to conform it to the schema and data provided):

IF OBJECT_ID(N'dbo.UnitRate', N'U') IS NOT NULL
  DROP TABLE dbo.UnitRate;

CREATE TABLE dbo.UnitRate (
    UnitRateID SMALLINT PRIMARY KEY
    , UnitRate SMALLMONEY
    , PropertyID SMALLINT 
    );

INSERT INTO dbo.UnitRate VALUES 
  (11, 300, 22000)
  , (12, 350, 23450);

GO

IF OBJECT_ID(N'dbo.CalculateDeposit', N'FN') IS NOT NULL
  DROP FUNCTION dbo.CalculateDeposit; 

GO

CREATE FUNCTION dbo.CalculateDeposit
(
    @UnitRateID smallint
)
RETURNS smallmoney
AS
    BEGIN
        DECLARE @UnitRate smallmoney

        SET @UnitRate = (
            SELECT ur.UnitRate
            FROM UnitRate as ur
            WHERE ur.UnitRateID = @UnitRateID
        )
        RETURN @UnitRate
    END;
GO

Test the result:

SELECT UnitRateID, UnitRate, PropertyID, dbo.CalculateDeposit(UnitRateID) as FN_UnitRate
FROM dbo.UnitRate;

Output:

UnitRateID  UnitRate    PropertyID  FN_UnitRate
11          300.00      22000       300.00
12          350.00      23450       350.00

As you can see, the function returns always the correct value of UnitRate associated to UnitRateID.

I think that the problem must be somewhere else...

I hope this approach will help you.

Upvotes: 1

Related Questions