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

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)

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)

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.