Question on user defined function (SQL Server 2000) 
Author Message
 Question on user defined function (SQL Server 2000)

Good afternoon,

Quite possibly a very simple solution to this ... novice at creating UDFs in T-SQL (SQL Server 2000).

I'm writing a function to calculate an invoice's total, based on the sum of its associated line items.

The line items are stored in tblInvoiceLineItems, and the field containing the price is ItemAmt (type smallmoney, no nulls). The parameter of InvoiceID (type int) is passed.

Following is the text for my function:
----------------------------------------

RETURNS SMALLMONEY
AS
BEGIN





END
----------------------------------------

When I execute this from the query analyzer, I receive the following message:

PRINT dbo.fncCalcInvoiceAmt(1)
Server: Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type smallmoney to nvarchar is not allowed. Use the CONVERT function to run this query.

The function does not return an error, if the 2nd line is changed from RETURNS SMALLMONEY to RETURNS INT.

Where is SQL telling me that I need to place a CONVERT function?

Thanks in advance,
Bryant

--

Bryant Farley
Strategic Management Concepts, LLC
Web Design & Database Solutions
http://www.***.com/



Thu, 06 Jan 2005 04:52:17 GMT
 Question on user defined function (SQL Server 2000)

hmmm...can't seem to reproduce on sql2k/sp2. the function works just fine. perhaps, it's your calling 'select'.

use tempdb
go
create table tblInvoiceLineItems(invoiceid int, itemamt smallmoney)
insert tblInvoiceLineItems
select orderid,unitprice
from northwind..[order details]
go

RETURNS SMALLMONEY
AS
BEGIN




END
go
select dbo.fncCalcInvoiceAmt(10),dbo.fncCalcInvoiceAmt(10248)
go
drop function dbo.fncCalcInvoiceAmt
go
drop table tblInvoiceLineItems

--
-oj
Rac v2.1 coming soon
http://www.rac4sql.net


  Good afternoon,

  Quite possibly a very simple solution to this ... novice at creating UDFs in T-SQL (SQL Server 2000).

  I'm writing a function to calculate an invoice's total, based on the sum of its associated line items.

  The line items are stored in tblInvoiceLineItems, and the field containing the price is ItemAmt (type smallmoney, no nulls). The parameter of InvoiceID (type int) is passed.

  Following is the text for my function:
  ----------------------------------------

  RETURNS SMALLMONEY
  AS
  BEGIN





  END
  ----------------------------------------

  When I execute this from the query analyzer, I receive the following message:

  PRINT dbo.fncCalcInvoiceAmt(1)
  Server: Msg 257, Level 16, State 3, Line 2
  Implicit conversion from data type smallmoney to nvarchar is not allowed. Use the CONVERT function to run this query.

  The function does not return an error, if the 2nd line is changed from RETURNS SMALLMONEY to RETURNS INT.

  Where is SQL telling me that I need to place a CONVERT function?

  Thanks in advance,
  Bryant

  --

  Bryant Farley
  Strategic Management Concepts, LLC
  Web Design & Database Solutions
  http://www.smc-llc.com



Thu, 06 Jan 2005 05:05:40 GMT
 Question on user defined function (SQL Server 2000)

Works for me this way too ... dunno what the prob is, lack of caffeine & general cluelessness as always may be the culprit  :)

Thanks for your kind assistanc3!

Yours,
Bryant

  hmmm...can't seem to reproduce on sql2k/sp2. the function works just fine. perhaps, it's your calling 'select'.

  use tempdb
  go
  create table tblInvoiceLineItems(invoiceid int, itemamt smallmoney)
  insert tblInvoiceLineItems
  select orderid,unitprice
  from northwind..[order details]
  go

  RETURNS SMALLMONEY
  AS
  BEGIN




  END
  go
  select dbo.fncCalcInvoiceAmt(10),dbo.fncCalcInvoiceAmt(10248)
  go
  drop function dbo.fncCalcInvoiceAmt
  go
  drop table tblInvoiceLineItems

  --
  -oj
  Rac v2.1 coming soon
  http://www.rac4sql.net


    Good afternoon,

    Quite possibly a very simple solution to this ... novice at creating UDFs in T-SQL (SQL Server 2000).

    I'm writing a function to calculate an invoice's total, based on the sum of its associated line items.

    The line items are stored in tblInvoiceLineItems, and the field containing the price is ItemAmt (type smallmoney, no nulls). The parameter of InvoiceID (type int) is passed.

    Following is the text for my function:
    ----------------------------------------

    RETURNS SMALLMONEY
    AS
    BEGIN





    END
    ----------------------------------------

    When I execute this from the query analyzer, I receive the following message:

    PRINT dbo.fncCalcInvoiceAmt(1)
    Server: Msg 257, Level 16, State 3, Line 2
    Implicit conversion from data type smallmoney to nvarchar is not allowed. Use the CONVERT function to run this query.

    The function does not return an error, if the 2nd line is changed from RETURNS SMALLMONEY to RETURNS INT.

    Where is SQL telling me that I need to place a CONVERT function?

    Thanks in advance,
    Bryant

    --

    Bryant Farley
    Strategic Management Concepts, LLC
    Web Design & Database Solutions
    http://www.smc-llc.com



Thu, 06 Jan 2005 05:21:55 GMT
 Question on user defined function (SQL Server 2000)

Quote:

> PRINT dbo.fncCalcInvoiceAmt(1)
> Server: Msg 257, Level 16, State 3, Line 2
> Implicit conversion from data type smallmoney to nvarchar is not allowed.
> Use the CONVERT function to run this query.

There is right there. PRINT expects a character expression, and you are
feeding it smallmoney.

Quote:
> The function does not return an error, if the 2nd line is changed from
> RETURNS SMALLMONEY to RETURNS INT.

Yes, in this case there is an implicit conversion.

A general note: be wary with scalar UDF. They tend to transform a query
the regular set-based handling, into something cursor-like which may
have dreadful effects on performance.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Thu, 06 Jan 2005 06:03:21 GMT
 Question on user defined function (SQL Server 2000)
erland,

good catch!

he's doing a print instead of select.

--
-oj

Quote:

> There is right there. PRINT expects a character expression, and you are
> feeding it smallmoney.



Thu, 06 Jan 2005 06:17:33 GMT
 Question on user defined function (SQL Server 2000)

Quote:
> A general note: be wary with scalar UDF. They tend to transform a query
> the regular set-based handling, into something cursor-like which may
> have dreadful effects on performance.

        Is this always the case? I have a rather important function in one
db that I would like to ensure is highly-optimized since it is used as a
field in many other SELECTs.

--

(Any opinions expressed are strictly mine only and not my employer's)
--------------------------------------------------------------------
Paul Tiseo, Intermediate Systems Programmer



Sun, 09 Jan 2005 21:05:23 GMT
 Question on user defined function (SQL Server 2000)
Paul

Quote:
> Is this always the case?

Yes ... scalar UDF's, in SQL Server, are always implemented in such a manner
as to emulate cursor type processing.
From a pure performance viewpoint, table valued UDF's perform significantly
better.

However, performance should be just one of many factors that you consider
when creating a database application. Scalar UDF's do have the advantage of
encapsulating logic in a single place, thus simplifying maintenance. In
particular, by encapsulating logic in a scalar UDF, one can easily change
the logic in a single place and know that all references to the logic will
be affected automatically, rather than having to hunt down instances of
repeated code in multiple stored procedures and the such.

Obviously, you have to make a determination that balances performance vs.
ease of maintenance, and to do so, I would recommend that you do experiments
to determine the performance impact that scalar UDF's are likely to have on
your particular application. Remember that if your queries tend to return
small result sets, then a scalar UDF in the SELECT list is likely to have
minimal impact. Having a scalar UDF in a WHERE clause or processing large
result sets is more likely to have noticeable performance impact.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



Quote:

> > A general note: be wary with scalar UDF. They tend to transform a query
> > the regular set-based handling, into something cursor-like which may
> > have dreadful effects on performance.

> Is this always the case? I have a rather important function in one
> db that I would like to ensure is highly-optimized since it is used as a
> field in many other SELECTs.

> --

> (Any opinions expressed are strictly mine only and not my employer's)
> --------------------------------------------------------------------
> Paul Tiseo, Intermediate Systems Programmer




Mon, 10 Jan 2005 03:15:18 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Question on user defined function (SQL Server 2000)

2. Need help with SQL Server 2000 - user defined functions

3. User defined function in sql server 2000

4. Need help with SQL Server 2000 - user defined functions

5. call a user defined function from another user-defined function

6. SQL 2000 User Defined Function with Dynamic SQL

7. Newbie questions on User-defined functions in SQL server

8. Sql 2000 Calculated Fields and User Defined Functions

9. SQL 2000 User Defined Functions

10. SQL 2000 User Defined Functions

11. User Defined Functions - SQL 2000

12. Calling SQL 2000 User Defined function from ADO


 
Powered by phpBB® Forum Software