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/

--

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



Thu, 06 Jan 2005 04:55:34 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

  --

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



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

works fine here ... how are you trying to use the function ?

  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

  --

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



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

As aptly demonstrated by oj and Laurie, posting code fragments that can not
be cut and pasted into Query Analyzer to reproduce the error message you are
getting is of little value.

What is needed is a repro script. Code that can be cut and pasted into Query
Analyzer to reproduce the error message. A repro script would typically
contain, at a minimum, (simplified) table schemas (CREATE TABLEs) and sample
data (INSERTs).

-------------------------------------------
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.


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

--

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



Thu, 06 Jan 2005 07:35:28 GMT
 Question on user defined function (SQL Server 2000)

You get this message because you're using print function to test it. Instead of

PRINT dbo.fncCalcInvoiceAmt(1)

for testing use

select dbo.fncCalcInvoiceAmt(1)

Regards
    Kresimir Radosevic

  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

  --

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



Thu, 06 Jan 2005 14:03:24 GMT
 
 [ 5 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