Calculations in stored procedures
-
Hello,
I am new to T-SQL as I have been dealing with MS Access before. I hope that someone can help me with one specific problem.
Here is my stored procedure:
CREATE PROCEDURE EQDailySettlement AS
DECLARE
@ExecCharge float
SELECT
@ExecCharge = Fee * ABS(Quantity) + ExecChargeAdjustment,
ExecCharge = @ExecCharge,
NetBroker = RateBroker * Quantity + @ExecCharge,
FROM
Trades
However I am getting the following error message:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Please could you advise on how to accomplis the required calculation. Thank you very much in advance. I could easily retype the expression but I would really like to learn how to do it properly.
-
SELECT
@ExecCharge = Fee * ABS(Quantity) + ExecChargeAdjustment,
ExecCharge = @ExecCharge,
NetBroker = RateBroker * Quantity + @ExecCharge,
FROM
Trades
Separate them into two operations:
1. SELECT .. here goes all variable assignment
2. Calculation
Also, you haven't specified what values are assigned to the variable, since you have neither a WHERE clause nor a ORDER BY. T-SQL will assign the values to the variable from the *last* row retrieved, which might not be what you want.
Wouldn't this do the same?
SELECT
NetBroker = RateBroker * Quantity + (Fee * ABS(Quantity) + ExecChargeAdjustment)
FROM
Trades
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
Thank you for your answer.
The last lines that you suggested would do the same, but it would be inconvenient if I need to reuse "ExecCharge" in 3 or 4 different calculations. That would mean retyping the same chunk several times. Is there any other, more modular way?
-
Maybe I didn't express myself clearly. Consider this:
USE NORTHWIND
DECLARE @unit FLOAT
SELECT
@unit = UnitPrice * Quantity
FROM
[Order Details]
SELECT
@unit
SELECT
@unit = UnitPrice * Quantity
FROM
[Order Details]
ORDER BY
OrderID DESC
SELECT
@unit
-----------------------------------------------------
26.0
(1 row(s) affected)
-----------------------------------------------------
168.0
(1 row(s) affected)
SQL Server will assign the value to the variable based on the last row processed. As you can see this can lead to different values based on how you order your statement.
Since I suspect neither "Quantity" nor "ExecChargeAdjustment" to be constant among all rows in your table, how can you tell which values to be used for your calculation?
And unfortunately you can't use the interim assignment to a variable for further calculation. This will yield your original error. So I think you have to count in some more keystrokes here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
thank you for explaining this to me! At least I will feel better when typing repetitive strings!
-
Sorry about that.
You might have a look at UDF's to modularize your code. However, test this *very* carefully if they meet your performance objectives. They can really slow down things.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>>That would mean retyping the same chunk several times. Is there any other, more modular way?<<
Yes there is! Use a Subquery and you will be happy
Select
ExecCharge,
NetBroker = (RateBroker * Quantity + ExecCharge)
AnotherField = ExecCharge * 100 -- just an example
FROM
(
SELECT
* ,
(Fee * ABS(Quantity) + ExecChargeAdjustment) as ExecCharge
FROM
Trades
) SubQ
hth
* Noel
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply