July 15, 2012 at 10:15 am
Have created Scalar User Defined Function and when i try to execute the function it fails.What could be the problem
Please find the function as below.
CREATE FUNCTION fnContactFullName(@ID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @FullName NVARCHAR(255)
SELECT @FullName=FirstName+''+MiddleName+''+LastName
FROM Person.Contact
WHERE ContactID=@ID
return @FullName
END
I am trying to execute the Scalar User Defined Function using sytax below.
SELECT * FROM fnContactFullName(1)
July 15, 2012 at 10:30 am
Try:
SELECT dbo.fnContactFullName(1)
and that will work.
You're confusing two different types of function. There are Scalar Functions, which you would use within the SELECT part of a query and Table Value Functions (TVF) which can be used in the FROM part of a query.
Have a look at: http://msdn.microsoft.com/en-us/library/ms177499(v=sql.105).aspx
July 15, 2012 at 10:31 am
Smash125 (7/15/2012)
Have created Scalar User Defined Function and when i try to execute the function it fails.What could be the problemPlease find the function as below.
CREATE FUNCTION fnContactFullName(@ID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @FullName NVARCHAR(255)
SELECT @FullName=FirstName+''+MiddleName+''+LastName
FROM Person.Contact
WHERE ContactID=@ID
return @FullName
END
I am trying to execute the Scalar User Defined Function using sytax below.
SELECT * FROM fnContactFullName(1)
Syntax Error.This is not TVF.
So use it.
SELECT dbo.fnContactFullName(1)
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 15, 2012 at 10:35 am
Thanks it worked and thanks for clarifying !!!!
July 16, 2012 at 8:49 am
Smash125 (7/15/2012)
Have created Scalar User Defined Function and when i try to execute the function it fails.What could be the problemPlease find the function as below.
CREATE FUNCTION fnContactFullName(@ID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @FullName NVARCHAR(255)
SELECT @FullName=FirstName+''+MiddleName+''+LastName
FROM Person.Contact
WHERE ContactID=@ID
return @FullName
END
I am trying to execute the Scalar User Defined Function using sytax below.
SELECT * FROM fnContactFullName(1)
MANY problems/issues here:
1) Avoid scalar UDFs at almost any cost!! They are horrible. Please see my chapter in the SQL Server MVP Deep Dives 2 book entitled "Death by UDF".
2) What happens to your output if any of the 3 columns is NULL?
3) Why did you define the output to be nvarchar(100) and the variable to be nvarchar(255)?? You could have a data truncation failure here. Your output (and the variable used) should be EXACTLY the max size of all 3 columns combined. There is never a reason to not use the exact datatype when developing against a database table since the schema is known at dev time. Sometimes using the wrong datatype is DISASTROUSLY BAD from a performance AND concurrency perspective!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply