Help with a simple Function
-
I'm trying to create a function which returns a LastName, Firstname from a ID. So far I have the stuff below but it is coming up as null when I run a query such as
select LastName, FirstName, dbo.MemberName(RegMemberID) as MemberName
from RegisteredMembers
--- FUNCTION BELOW ----
USE
Sports;
GO
CREATE
FUNCTION dbo.MemberName(@RegMemberID varchar(3))
RETURNS
nvarchar(42) WITH EXECUTE AS CALLER
AS
begin
DECLARE @fullname nvarchar(42)
SELECT @fullname = (LastName + ', ' + FirstName)
FROM dbo.RegisteredMembers
WHERE @RegMemberID = RegMemberID;
RETURN @fullname;
end
GO
USE
Sports
GO
-
You probably have a null in either LastName or Firstname. ( null + 'Anything' = null)
There is no need for a function as something like the following should work:
SELECT LastName
,FirstName
,CASE WHEN LastName IS null and FirstName IS null
THEN 'Unknown'
WHEN LastName IS null
THEN FirstName
WHEN Firstname IS null
THEN LastName
ELSE LastName + ', ' + FirstName
END AS MemberName
FROM dbo.RegisteredMembers
-
Yeah, Thanks for that. But I am doing this assignment and they require me to do it as a function so I can use it for other things throughout the assignment. I was able to do it as a procedure but not as a function
I checked to make sure there were no null values in the database and there aren't. So maybe that isn't the reason it is giving me a null? I'm sure I constructed the function wrong or something is missing.
-
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply