April 11, 2003 at 3:57 pm
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
)
April 12, 2003 at 11:08 am
Do you not get any rows or just the function field that is empty? Does the customer always have a note? You could try an outer join to see if you still don't get any rows. You might want to try running the function with hard-coded values where you know it should return data instead of in a join clause.
Darren
Darren
Darren
April 13, 2003 at 10:14 am
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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply