scalar function.

Question

in scalar function how to write in returns value two type of data type
e.g.
create function xyz
(
@eid int
@ename varchar(20)
@esalary float
)
returns int,varchar(20),float       ..........................is it right????
ass begin
return (select@eid+'  '+@ename+'  '+@esalary);
end

Gajendra Jangid · Answer

hello dear,   In scalar  function you can't return multiple value, if do you want to return multiple value the use tablular function. if do you want to use scalar funtion then you can return all value with comma saperate and return type will be varchar/nvarchar create   function  xyz   (   @eid  int    @ename  varchar (20)   @esalary  float    )   returns   varchar ( max )    ass  begin     return  ( select  @eid+ ',' +@ename+ ',' +@esalary);   end           select  STRING_SPLIT ( ( select  dbo.xyz(1, 'ans' ,232)),  ','  )

Rajanikant Hawaldar · Answer

Hi Faraz Akhtar, Its Right, look here USE AdventureWorks2008R2;   GO   IF OBJECT_ID(N 'dbo.ufnGetContactInformation' , N 'TF' )  IS   NOT   NULL    DROP   FUNCTION  dbo.ufnGetContactInformation;   GO   CREATE   FUNCTION  dbo.ufnGetContactInformation(@ContactID  int )   RETURNS  @retContactInformation  TABLE    (   -- Columns returned by the function    ContactID  int   PRIMARY   KEY   NOT   NULL ,   FirstName nvarchar(50)  NULL ,   LastName nvarchar(50)  NULL ,   JobTitle nvarchar(50)  NULL ,   ContactType nvarchar(50)  NULL    )   AS    -- Returns the first name, last name, job title, and contact type for the specified contact.    BEGIN    DECLARE    @FirstName nvarchar(50),   @LastName nvarchar(50),   @JobTitle nvarchar(50),   @ContactType nvarchar(50);   -- Get common contact information    SELECT    @ContactID = BusinessEntityID,   @FirstName = FirstName,   @LastName = LastName   FROM  Person.Person   WHERE  BusinessEntityID = @ContactID;   -- Get contact job title    SELECT  @JobTitle =   CASE    -- Check for employee    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'EM' )   THEN  ( SELECT  JobTitle   FROM  HumanResources.Employee  AS  e   WHERE  e.BusinessEntityID = @ContactID)   -- Check for vendor    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'VC' )   THEN  ( SELECT  ct. Name    FROM  Person.ContactType  AS  ct   INNER   JOIN  Person.BusinessEntityContact  AS  bec   ON  bec.ContactTypeID = ct.ContactTypeID   WHERE  bec.PersonID = @ContactID)   -- Check for store    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'SC' )   THEN  ( SELECT  ct. Name    FROM  Person.ContactType  AS  ct   INNER   JOIN  Person.BusinessEntityContact  AS  bec   ON  bec.ContactTypeID = ct.ContactTypeID   WHERE  bec.PersonID = @ContactID)   ELSE   NULL    END ;   -- Get contact type    SET  @ContactType =   CASE    -- Check for employee    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'EM' )   THEN   'Employee'    -- Check for vendor    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'VC' )   THEN   'Vendor Contact'    -- Check for store    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'SC' )   THEN   'Store Contact'    -- Check for individual consumer    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'IN' )   THEN   'Consumer'    -- Check for general contact    WHEN  EXISTS( SELECT  *  FROM  Person.Person  AS  p   WHERE  p.BusinessEntityID = @ContactID  AND  p.PersonType =  'GC' )   THEN   'General Contact'    END ;   -- Return the information to the caller    IF @ContactID  IS   NOT   NULL    BEGIN    INSERT  @retContactInformation   SELECT  @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;   END ;   RETURN ;   END ;   GO USE AdventureWorks2008R2;   GO   SELECT  ContactID, FirstName, LastName, JobTitle, ContactType   FROM  dbo.ufnGetContactInformation(1209);   GO   SELECT  ContactID, FirstName, LastName, JobTitle, ContactType   FROM  dbo.ufnGetContactInformation(5);   GO   https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191165(v=sql.105)

Priyanka Jain · Answer

As per details i have updated your SQL Query.
CREATE   FUNCTION  xyz   (        @eid  INT ,        @ename  VARCHAR (20),        @esalary FLOATT   )   returns   VARCHAR ( MAX )   AS   BEGIN     return  ( SELECT  CONCAT(@eid, ',' ,@ename, ',' ,@esalary));   END        SELECT  dbo.xyz(1, 'text' ,100)

Faraz Akhtar · Answer

any other suggetion or correction in my program.

Suraj Kumar · Answer

In scalar function you can not multiple data type value. But you can do it by table valued function as suggested by @Rajanikant.   The other way of doing this type of job is returning different values in comma separated value and then again stract and use as per your business logic.   for e.g., you declare a varchar(20) datatype variable and assigning that value to 'SURAJ,20,9049...'. Consider this value will be returned then after extract you can use that in three different varible name, age and contact number after function return and extract.