### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 4:36pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/1 "2008-11-17T16:36:21Z")
I am often having to do “select **sum(cast(total\_bet\_amount as real) - cast(refund\_amt as real)) as handle** from…”
As you can see that bit in bold is a bit of a mouthfull. Would it be possible to write a function to do this, so in my future sql statements I can type “select **hndl()** from…” instead?
---
### Author:  [@wolfman](https://boards.straightdope.com/u/wolfman)
#### Post date: [November 17, 2008, 4:40pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/2 "2008-11-17T16:40:36Z")
yep. That’s exactly what functions are for.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 4:50pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/3 "2008-11-17T16:50:04Z")
Well, urr, how do I do it?
I can probably work it out but I figured I’d get you guys to do it for me 😃
I’ll go work it out.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 4:56pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/4 "2008-11-17T16:56:29Z")
I get errors about unknown column names, so I wouldn’t be able to do it without making it a COMPLETE sql statement - which in turn stops it doing what I want it to do… (be part of an sql statement)
is it possible to create it like a #define in c? In other words the sql engine will find the word ‘hndl()’ and replace it with "sum(cast(total\_bet\_amount as real) - cast(refund\_amt as real)) as handle "
---
### Author:  [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)
#### Post date: [November 17, 2008, 5:18pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/5 "2008-11-17T17:18:43Z")
[QUOTE=Lobsang]
is it possible to create it like a #define in c?
[/QUOTE]
Nope (unless you’re writing in Pro\*C)
This works in Oracle
```auto
create or replace function hndl( a number, b number ) return number as
begin
return a - b;
end;
```
This is very bad practice (database level function, it should be in a package).
Then call in a query with parameters.
```auto
SQL> select hndl( 42, 22 ) from dual;
HNDL(42,22)
-----------
20
```
Or more usefully use columns from your query table, something like:
```auto
select hndl(BET_AMOUNT, REFUND_AMOUNT)
from LOBSANGS_TABLE
where <>
```
---
### Author:  [@redtail23](https://boards.straightdope.com/u/redtail23)
#### Post date: [November 17, 2008, 5:37pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/6 "2008-11-17T17:37:53Z")
**Lobsang** , are you still working in Access? I can tell you how to set up a function in either Access or MS-SQL, but I’d need to know which you were using.
---
### Author:  [@Raza](https://boards.straightdope.com/u/Raza)
#### Post date: [November 17, 2008, 5:38pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/7 "2008-11-17T17:38:56Z")
I didn’t see a mention of what database system you are using (I may have missed it), but for SQL Server:
create function TestFunc
(@bet\_amt varchar(15), @refund\_amt varchar(15))
returns real
as
begin
declare @return real
set @return = cast(@bet\_amt as real) - cast(@refund\_amt as real)
return @return
end
My assumption is that you’re passing some sort of character value, since you used a CAST in your original example.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 5:48pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/8 "2008-11-17T17:48:11Z")
> [@redtail23](#):
>
> **Lobsang** , are you still working in Access? I can tell you how to set up a function in either Access or MS-SQL, but I’d need to know which you were using.
Nah I’ve never used access for work stuff.
I’m currently using microsoft server management studio express (for sql server 2005)
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 5:50pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/9 "2008-11-17T17:50:24Z")
> [@Raza](#):
>
> I didn’t see a mention of what database system you are using (I may have missed it), but for SQL Server:
>
> create function TestFunc
> (@bet\_amt varchar(15), @refund\_amt varchar(15))
> returns real
> as
> begin
> declare @return real
> set @return = cast(@bet\_amt as real) - cast(@refund\_amt as real)
> return @return
> end
>
> My assumption is that you’re passing some sort of character value, since you used a CAST in your original example.
I want not to need to pass any values, since the same two fields are ALWAYS the same in this long string.
Sorry if that seemed snarky. Your code would probably work. It defeats the object a bit, but not totally. The object being not having to type all that long string just to get the handle (which is by far the most common bit of information I ever get)
If sql doesn’t have any equivalent of C’s #define feature then I guess I’ll live with it 😃
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [November 17, 2008, 8:24pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/10 "2008-11-17T20:24:49Z")
> [@Lobsang](#):
>
> I want not to need to pass any values, since the same two fields are ALWAYS the same in this long string.
>
> Sorry if that seemed snarky. Your code would probably work. It defeats the object a bit, but not totally. The object being not having to type all that long string just to get the handle (which is by far the most common bit of information I ever get)
> If sql doesn’t have any equivalent of C’s #define feature then I guess I’ll live with it 😃
With SQL server, I don’t think that you can get this as a true function without passing any values to it. If:
- you want to be a bit clever,
- total\_bet\_amount and refund\_amt are fields within the same table, and
- that field has a unique key,
then you could make the function take just one value, @key, and include lines of code declaring the parameters from **Raza** ’s version as temporary variables, then do
select @bet\_amt as total\_bet\_amount, @refund\_amt = refund\_amt from Lobsangtable where primarykey = @key
Alternately… you could try putting this expression in as a computed column, (also known as a formula column) in the table, or as an expression field in a view. This would allow you to access it as if it were an ordinary database field, without even needing to call the function.
While I’m at it - what datatype are the source fields and why do you constantly need to cast them into reals, if this is the most common bit of information you ever get? Is it possible that one of them will fail to convert to real? Why can’t the table fields be real in the first place? What do we mean by ‘reality’ anyways?
😃
---
### Author:  [@Raza](https://boards.straightdope.com/u/Raza)
#### Post date: [November 17, 2008, 9:41pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/11 "2008-11-17T21:41:31Z")
I’ll second ChrisK’s suggestion of a view, now that I know more about what you want.
And I agree that some sort of #DEFINE would be cool, but it’s just one more of those things that are in full-featured languages that are absent from T-SQL. But in fairness, they don’t work the same (T-SQL batches/stored procedures aren’t a _compile once_ sort of affair), so something like a #DEFINE is probably incompatible on several levels.
What we need is a good built-in Proper() function.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 9:54pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/12 "2008-11-17T21:54:12Z")
> [@chrisk](#):
>
> With SQL server, I don’t think that you can get this as a true function without passing any values to it. If:
>
> - you want to be a bit clever,
> - total\_bet\_amount and refund\_amt are fields within the same table, and
> - that field has a unique key,
>
> then you could make the function take just one value, @key, and include lines of code declaring the parameters from **Raza** ’s version as temporary variables, then do
> select @bet\_amt as total\_bet\_amount, @refund\_amt = refund\_amt from Lobsangtable where primarykey = @key
>
> Alternately… you could try putting this expression in as a computed column, (also known as a formula column) in the table, or as an expression field in a view. This would allow you to access it as if it were an ordinary database field, without even needing to call the function.
>
> While I’m at it - what datatype are the source fields and why do you constantly need to cast them into reals, if this is the most common bit of information you ever get? Is it possible that one of them will fail to convert to real? Why can’t the table fields be real in the first place? What do we mean by ‘reality’ anyways?
>
> 😃
They come from a table where nearly every field is a varchar data type. It was designed by somebody else, and it is populated by a pipe-delimited text file that comes from another system. It was badly designed. But it was designed as a ‘temporary measure’ and then it became permanent.
Would the ‘computed column’ become an actual column in the table? In other words would it require a modification of the table itself?
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 10:02pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/13 "2008-11-17T22:02:02Z")
I just googled it. It would require a table change. I might have to get my IT manager’s approval for that.
---
### Author:  [@redtail23](https://boards.straightdope.com/u/redtail23)
#### Post date: [November 17, 2008, 10:07pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/14 "2008-11-17T22:07:12Z")
> [@Lobsang](#):
>
> Would the ‘computed column’ become an actual column in the table? In other words would it require a modification of the table itself?
If you added a computed column to the table, it would require modification of the table to add a new column.
What would probably work better is to create a new view that selected whatever columns of the table you needed, plus your computed column. Then you use the view rather than the underlying table for your queries. You could also do all of your datatype conversions in the view, rather than in your TSQL code.
**chrisk** ’s other suggestion would also work, to send through only the key field of your table and calculate from there in the function. Although it doesn’t seem like it would be that much harder to just use the function properly and send through your two fields that need to be calculated.
“select hndl(total\_bet\_amt, refund\_amt)” isn’t that much longer than “select hndl(keyfield)”.
---
### Author:  [@Manduck](https://boards.straightdope.com/u/Manduck)
#### Post date: [November 17, 2008, 10:10pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/15 "2008-11-17T22:10:56Z")
I am of the opinion that you don’t need a function for this - it’s not that hairy a clause. So that’s one vote on the “nay” side 🙂
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 10:15pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/16 "2008-11-17T22:15:27Z")
> [@redtail23](#):
>
> If you added a computed column to the table, it would require modification of the table to add a new column.
>
> **What would probably work better is to create a new view that selected whatever columns of the table you needed, plus your computed column. Then you use the view rather than the underlying table for your queries. You could also do all of your datatype conversions in the view, rather than in your TSQL code.**
>
> **chrisk** ’s other suggestion would also work, to send through only the key field of your table and calculate from there in the function. Although it doesn’t seem like it would be that much harder to just use the function properly and send through your two fields that need to be calculated.
>
> “select hndl(total\_bet\_amt, refund\_amt)” isn’t that much longer than “select hndl(keyfield)”.
I like the idea of creating a view. Would doing it that way be computationally expensive? Because I’m guessing the view would be roughly a (select \*,new\_computed\_column) from table which would be a truly massive query (16,081,502 records), on which to run a smaller one.
so… say, **select handle from new\_view where date = 08/11/16** where new\_view is the view…
wouldn’t that be like running this… \*_select handle from (select ,sum(cast(total\_bet\_amount)-cast(refund\_amt)) as handle from orig\_table) where date = 08/11/16_
two queries, one of which is a MASSIVE result set.
Or is sql cleverer than that?
---
### Author:  [@Mbossa](https://boards.straightdope.com/u/Mbossa)
#### Post date: [November 17, 2008, 10:49pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/17 "2008-11-17T22:49:13Z")
I’ve read through this thread a couple of times, and maybe I haven’t had enough caffeine, but I can’t figure out what’s wrong with something simple like this:
```auto
CREATE FUNCTION TestFunc
()
RETURNS Int
AS
BEGIN
DECLARE @Result int
SELECT @Result = sum(cast(total_bet_amount as real) - cast(refund_amt as real)) FROM LobsangsMagicTable
RETURN @Result
END
GO
```
Usage:
```auto
SELECT dbo.TestFunc()
```
Unless you need to select from different tables every time you run it. That would complicate things.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 10:53pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/18 "2008-11-17T22:53:33Z")
> [@Mbossa](#):
>
> I’ve read through this thread a couple of times, and maybe I haven’t had enough caffeine, but I can’t figure out what’s wrong with something simple like this:
>
> ```auto
>
> CREATE FUNCTION TestFunc
> ()
> RETURNS Int
> AS
> BEGIN
> DECLARE @Result int
> SELECT @Result = sum(cast(total_bet_amount as real) - cast(refund_amt as real)) FROM LobsangsMagicTable
> RETURN @Result
> END
> GO
>
> ```
>
> Usage:
>
> ```auto
>
> SELECT dbo.TestFunc()
>
> ```
>
> Unless you need to select from different tables every time you run it. That would complicate things.
I’ll try that, but wouldn’t it just select the sum based on ALL the records (because there are no where clauses) regardless of what the where clause is in the sql statement the function is being called from?
---
### Author:  [@Mbossa](https://boards.straightdope.com/u/Mbossa)
#### Post date: [November 17, 2008, 10:55pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/19 "2008-11-17T22:55:40Z")
Ahhh…is the WHERE clause going to be different each time you run it? If so, that complicates it.
(if not, just stick it in the SELECT line in the function body)
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [November 17, 2008, 11:00pm UTC](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140/20 "2008-11-17T23:00:14Z")
Yeah the where clause will be different nearly every time I use it.
[Next page](https://boards.straightdope.com/t/can-i-convert-this-common-bit-of-sql-into-a-function/473140.md?page=2)