Purpose of SELECT before a calcuating the value of a function's return variable
-
In the code below what is the purpose of the SELECT statement before @SalesTaxDue = (@TaxRate * .01) * @Total? I would think that it would be sufficient to just have @SalesTaxDue = (@TaxRate * .01) * @Total without the SELECT statement, but doing so results in an error. I'm trying to understand the idea behind having SELECT appear before the function's return variable.
- michael.leach2015 - Tuesday, January 29, 2019 6:05 PMIn the code below what is the purpose of the SELECT statement before @SalesTaxDue = (@TaxRate * .01) * @Total? I would think that it would be sufficient to just have @SalesTaxDue = (@TaxRate * .01) * @Total without the SELECT statement, but doing so results in an error. I'm trying to understand the idea behind having SELECT appear before the function's return variable.
Try using just RETURN (@TaxRate * .01) * @Total
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, January 29, 2019 6:53 PMmichael.leach2015 - Tuesday, January 29, 2019 6:05 PMIn the code below what is the purpose of the SELECT statement before @SalesTaxDue = (@TaxRate * .01) * @Total? I would think that it would be sufficient to just have @SalesTaxDue = (@TaxRate * .01) * @Total without the SELECT statement, but doing so results in an error. I'm trying to understand the idea behind having SELECT appear before the function's return variable.
Try using just RETURN (@TaxRate * .01) * @Total
I tried your suggestion. Initially it returned repeating rows (a 2nd copy of each row) as if SQL thought that dbo.CalcSalesTax was an aggregate function. So to take care of that, I used GROUP BY and got the same answer. But why would SQL now return duplicate rows whereas before it did not? The only thing I did differently was remove the DECLARE line, the original RETURN line and changed SELECT to RETURN as you suggested. Here is the code which shows how I applied this function:
SELECT Invoices.InvoiceID, Customers.FirstName, Customers.LastName, Customers.State, InvoiceTotals.InvoiceTotal, SalesTaxRates.TaxRate, dbo.CalcSalesTax(InvoiceTotals.InvoiceTotal, SalesTaxRates.TaxRate) AS TaxDue
FROM InvoiceTotals INNER JOIN
Invoices ON InvoiceTotals.InvoiceID = Invoices.InvoiceID INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID INNER JOIN
SalesTaxRates ON Customers.State = SalesTaxRates.State
ORDER BY Invoices.InvoiceID;
Also, just to understand things, in my original code what is the SELECT statement actually doing? -
Would you post your original code as code as a graphic so that I don't have to type it out, please> Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, January 29, 2019 10:45 PM
CREATE FUNCTION dbo.CalcSalesTax (@Total money, @TaxRate decimal(4,2))RETURNS money
AS
BEGIN
DECLARE @SalesTaxDue money
SELECT @SalesTaxDue = (@TaxRate * .01) * @Total
RETURN @SalesTaxDueEND
-
The SELECT is there to assign a value to @SalesTaxDue. It's necessary because unlike in some other languages, you can't assign a value with an equality expression alone - you need an assignment keyword. SET will work as well in this case - I prefer it to SELECT when I'm setting the value of one variable only and there isn't a FROM clause. But as Jeff suggests, you can simplify it further:
CREATE FUNCTION dbo.CalcSalesTax (
@Total money
, @TaxRate decimal(4,2)
)
RETURNS money
AS
BEGIN
RETURN @TaxRate * 0.01 * @Total;
ENDYou may find you get better performance if you use an inline table-valued function, since scalar functions are evaluated individually for each row. Your function would then look like this:
CREATE FUNCTION dbo.CalculateSalesTax (
@Total money
, @TaxRate decimal(4,2)
)
RETURNS TABLE
AS RETURN
SELECT CAST(@TaxRate * 0.01 * @Total AS money) AS SalesTaxDue;You'd need to change your query as well, something like this:
SELECT
i.InvoiceID
, c.FirstName
, c.LastName
, c.State
, t.InvoiceTotal
, r.TaxRate
, x.SalesTaxDue
FROM InvoiceTotals t
INNER JOIN Invoices i ON t.InvoiceID = i.InvoiceID
INNER JOIN Customers c ON i.CustomerID = c.CustomerID
INNER JOIN SalesTaxRates r ON c.State = r.State
CROSS APPLY dbo.CalculateSalesTax(i.InvoiceTotal,r.TaxRate) x
ORDER BY i.InvoiceID;John
-
Remember some key differences when using SET or SELECT:
- SET can only assign a single value to a variable. If the expression being assigned returns multiple values (for example, from a sub-query), the SET statement will fail. SELECT will not fail, but it will assign one of the returned values to the variable.
- SET can assign a value to one variable per statement; SELECT can assign multiple values in the same statement.
- If the value returned by the expression is NULL, SET will set the variable to NULL. SELECT will retain the previous value of the variable without telling you - this may not be the result desired.
- gvoshol 73146 - Thursday, January 31, 2019 5:22 AM
This is good information for a beginner like me. You mentioned that SELECT will not fail, but will assign one of the return values to a variable if the expression returns multiple values. On the 2nd point, you then said SELECT can assign multiple values in the same statement. I'm confused. Can you give an example of how SELECT can assign multiple values in the same statement?
Also, if you have an expression returning multiple values and if SELECT will store only one of the returned values, how would you ensure that the assigned variable will store all of the values returned if you wanted that to happen?
-
You can code something like this for SELECT
SELECT
@a = 1,
@b = 2,
@C = 3
For SET, you'd need 3 separate statements
SET @a = 1
SET @b-2 = 2
SET @C = 3Regarding the multiple values found for an expression, suppose you were getting a value from a table.
SELECT ID FROM MyTableWithIDs -- this returns a list of ID'sSET @a = (SELECT ID FROM MyTableWithIDs) -- this would fail
SELECT @a = (SELECT ID FROM MyTableWithIDs) -- this would work, but it would select 1 of the ID values and you wouldn't know there were more than 1
- gvoshol 73146 - Thursday, January 31, 2019 8:45 AM
The examples with assigning values to the variables with SELECT and SET were very helpful.
Regarding the example where you have SELECT @a = (SELECT ID FROM MyTableWithIDs) I understand that only one ID would be assigned, but I was asking how could you store all returned values (not just one) in the variable @a so that you can reference @a (and therefore the entire set of values stored in @a) later on?
- michael.leach2015 - Thursday, January 31, 2019 10:25 AM
You would need to declare @a as a table typed variable. Usually, you don't want to code iterations over values in a table, you code once for the whole column. That's what we call RBAR or Row-By-Agonizing-Row.
- Luis Cazares - Thursday, January 31, 2019 11:09 AM
Table variable. That makes sense. Thank you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply