1

I have a stored procedure that accepts a parameter called @UserCurrency.

SELECT ItemID,    
CASE WHEN @UserCurrency=p.ItemCurrency THEN
       p.ItemPrice
   ELSE
       CASE WHEN p.ItemPrice = 999999999 THEN
            p.ItemPrice
       ELSE
            p.ItemPrice * CASE WHEN @UserCurrency = 'GBP' THEN c.GBP
             WHEN @UserCurrency = 'USD' THEN c.USD
             WHEN @UserCurrency = 'EUR' THEN c.EUR
             END
       END
   END As Price,
FROM Items p
LEFT JOIN CurrencyRates c
ON c.CurrencyFrom = p.ItemCurrency

Each item in the database has an ItemPrice (DECIMAL 10,0) that may be in one of several currencies (EUR, GBP, USD, ZAR, etc) depending on the item's location.

The CurrencyRates table has rows DECIMAL(5,2):

CurrencyFrom     EUR     USD     GBP
EUR              null    1.36    0.81
GBP              1.17    1.6     null
USD              0.71    null    0.63

On the website, a user can choose in which currency they wish to display items.

I check to see if the UserCurrency matches the ItemCurrency in my stored procedure. If YES, then I just select the ItemPrice as it requires no conversion. If NO, then I look up a table of currency conversion values to calculate the correct price to display through a JOIN.

My problem is that I can always get the values to work if I run the procedure on SSMS using the exact same values passed in by the web page. But if I view it on the webpage, the first Item has an ItemPrice but none of the subsequent records do.

If I remove the full CASE WHEN and just select ItemPrice on its own, all of the prices are shown on the website correctly. It seems to be the CASE WHEN tripping up ASP in some weird way.

SELECT ItemID,
ItemPrice
FROM Items

I have read that empty fields in an ASP recordset can be caused by a NVARCHAR(MAX), but there's no NVARCHAR(MAX) in my SELECT.

CONNECTION STRING

SET connect = server.CreateObject ("ADODB.connection")
dbstring = "Provider=SQLNCLI11;DataTypeCompatibility=80;Server=theserver.com; Database=thedatabase;uid=theuser;pwd=thepassword"
connect.Open dbstring

RECORDSET OPEN

items.open "ItemStoredProcedure " & var_PAGENUMBER & "," & var_ITEMSPERPAGE & "," & var_SORTING & "," & var_USER_CURRENCY

LOOP

IF NOT items.EOF THEN

items.MOVEFIRST

DO WHILE NOT items.EOF

response.write items("Price")
items.MOVENEXT
LOOP

END IF

What is going on? Please help!

FULL DISCLOSURE

  • There are nvarchar(max) fields within the Items table
  • I do want to select nvarchar(max) fields in this same procedure, but for the purposes of testing this problem they are completely commented out
  • There are other int and varchar fields in the select but I haven't included them for clarity
user692942
  • 16,398
  • 7
  • 76
  • 175
TVRV8S
  • 69
  • 10
  • A question full of assumptions, the `CASE` is executed at the SQL Server regardless of where it comes from so the likelihood is that you are not passing the same data when you execute it from SSMS and the Web Application. Ironically though the one piece of data we would need you decided not to provide which is the ASP code. We need to see how you call the stored procedure in ASP. – user692942 Sep 20 '16 at 10:16
  • Yes, that is what's confusing me: the fact that the CASE should have no bearing on this but commenting it out allows the prices to display on the webpage correctly. I am definitely passing in the same parameters to the stored procedure. I have already done quite a bit of research and checked and checked several times. I didn't think the ASP call was particularly relevant as I use exactly the same calls successfully elsewhere and have done for years and all it's doing is passing in a page number (int), number of items per page (int), the UserCurrency (varchar 3) and sorting column. – TVRV8S Sep 20 '16 at 11:17
  • The page displays entirely correctly whether I use the CASE WHEN or not. It is only the price field that appears to trip up when I use CASE WHEN. The first record in the recordset will have the price, but all others will have nothing at all. – TVRV8S Sep 20 '16 at 11:20
  • Like I said before you need to show some ASP code, preferably how you execute the query and pass the `@UserCurrency` parameter. – user692942 Sep 20 '16 at 11:40
  • First off I'd place `"ItemStoredProcedure " & var_PAGENUMBER & "," & var_ITEMSPERPAGE & "," & var_SORTING & "," & var_USER_CURRENCY` in a variable somehing like `sql = "ItemStoredProcedure " & var_PAGENUMBER & "," & var_ITEMSPERPAGE & "," & var_SORTING & "," & var_USER_CURRENCY` then call that like `items.open sql` that way you can easily `Response.Write sql` to check the command you are executing. I'd start there, as you are not sending the parameters using `ADODB.Command`. I wouldn't recommend this here is example of an [`ADO.Command` approach](http://stackoverflow.com/a/21944948/692942). – user692942 Sep 20 '16 at 11:43
  • I had checked this before but have done it again. I can confirm that the input from the ASP page is exactly the same as the direct input in SSMS. – TVRV8S Sep 20 '16 at 11:52
  • 1
    That link is VERY useful. I've never figured out how to do parameters in the ASP and that should help. Many thanks. – TVRV8S Sep 20 '16 at 11:54

1 Answers1

1

I think the issue is not with the ASP code, it's with you SP, you need to re-write the code in following manner by resetting null values to 1 using COALESCE method

SELECT ItemID,    
    CASE WHEN p.ItemPrice = 999999999 THEN
        p.ItemPrice
    ELSE
        p.ItemPrice * (CASE WHEN @UserCurrency = 'GBP' THEN COALESCE(c.GBP, 1)
             WHEN @UserCurrency = 'USD' THEN COALESCE(c.USD, 1)
             WHEN @UserCurrency = 'EUR' THEN COALESCE(c.EUR, 1)
             ELSE 1
             END)
    END As Price,
FROM Items p
LEFT JOIN CurrencyRates c
ON c.CurrencyFrom = p.ItemCurrency
Shailesh Kopardekar
  • 91
  • 4