No output from function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

No output from function

Author

Topic

westmich
Starting Member

35 Posts

Posted - 2003-04-11 : 17:58:42

OK - I am pretty new at writing functions, however, I am not getting any errors or problems - simply nothing is coming out.

I have a customer table with a first name, last name, and an optional spouse name. There are one or more call notes for each customer. I am creating an output report that shows the customer name followed by his or her call notes. I had no problems until I deceided to add the spouse name like - "Steve (Vicki) Olson". If I didn't have a spouse, then it would simply be - "Steve Olson".

Here is what I am working with:

-- query --
Select dbo.getFullCustName(c.CustomerID) as FullName, n.Created, n.Notes
From MT_Cust_Notes n, MT_Customers c
Where c.CustomerID = n.CustomerID
And n.Created >= '1/4/2003'
And n.Created <= '4/11/2003'
Order By FullName

-- function --
CREATE FUNCTION [dbo].[getFullCustName] (@CustID INT)
RETURNS varchar(85) AS
BEGIN
Declare @FullName varchar(85)

If (Select SpouseName From MT_Customers Where MemberID = @CustID) Is Not NULL
Select @FullName = (Select Fname + ' (' + SpouseName + ') ' + Lname as LongName From MT_Customers Where MemberID = @CustID)
Else
Select @FullName = (Select Fname + ' ' + Lname as LongName From MT_Customers Where MemberID = @CustID)

Return @FullName
END

-- customers table (abbrviated) --
CREATE TABLE [dbo].[MT_Customers] (
CustomerID int IDENTITY (1, 1) NOT NULL,
MemberID int NOT NULL,
Fname varchar(25) NOT NULL,
Lname varchar(35) NOT NULL,
SpouseName varchar(25) NULL
)

-- customer call notes table --
CREATE TABLE [dbo].[MT_Cust_Notes] (
NoteID int IDENTITY (1, 1) NOT NULL,
CustomerID int NOT NULL,
MemberID int NOT NULL,
Created smalldatetime NOT NULL,
Notes text NOT NULL
)

Edited by - westmich on 04/12/2003 10:54:53

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-12 : 11:54:00

Just take the meat of your function (between the CREATE and RETURN), put it in the query analyzer as a set of T-SQL statements, put in a dummy parameter and test it out yourself to see what is happening.

That is the best way to write/troublesheet functions. If that doesn't reveal the answer, let us know.

- Jeff

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-12 : 15:06:49

A few things.
First you can simplify your function this way:


ALTER FUNCTION [dbo].[getFullCustName] (@CustID INT) 
RETURNS varchar(85) AS 
BEGIN 
Declare @FullName varchar(85) 
Select @FullName = (Select Fname + 
case when SpouseName IS NOT NULL then ' (' + SpouseName + ') ' else ' ' end 
+ Lname as LongName From MT_Customers Where MemberID = @CustID) 
Return @FullName 
END

Unless MemberID is unique, you might end up with more than one row inside the function which will cause an error.

If there is no row for a customer in the MT_Cust_Notes table, you will end up with no results, since you are using INNER JOIN.
Change it to OUTER JOIN. something like


Select dbo.getFullCustName(c.CustomerID) as FullName, n.Created, n.Notes 
From MT_Customers c
left outer join  MT_Cust_Notes n
on c.CustomerID = n.CustomerID 
where n.Created IS NULL OR ( n.Created >= '1/4/2003' 
And n.Created <= '4/11/2003 23:59' ) 
Order By FullName

Bambola.

westmich
Starting Member

35 Posts

Posted - 2003-04-13 : 12:10:31

Thanks for the help!

The problem was with the MemberID, I don't know what I was thinking :(

I changed it to 'where CustomerID = @CustID' and it works fine now.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources