CODEWITHSUNDEEP
sqlsql-serverfunction
John
John

Reputation: 171

Sql query with function that return 2 values

How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server inside a query?

e.g select col1, dbo.function1(col2) from table

and the result to be

Col1 |        Col2       |         Col3
-----+-------------------+---------------------
x    | Num from function | String from function

I don't want to call the function 2 times because i use some complex code and i don't want to execute it twice. Just to take the final 2 results in the Return

Upvotes: 4

Views: 64905

Answers (3)

vmvadivel
vmvadivel

Reputation: 1067

Extract from - http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table

--Sample Table
CREATE TABLE emails 
(
    ID INT PRIMARY KEY, 
    EmailAddress VARCHAR(100)
)
GO

--Dummy Data
INSERT INTO emails
SELECT 1,'[email protected]' UNION ALL
SELECT 2,'[email protected]' UNION ALL
SELECT 3,'[email protected]'

--UDF Creation
CREATE FUNCTION EmailParse (@email VARCHAR(1000))
RETURNS @t TABLE (UserName VARCHAR(20), Domain VARCHAR(20))
AS
BEGIN
    DECLARE @i INT

    SELECT @i = charindex('@', @email,1);

    IF (@i > 1)
        INSERT INTO @t VALUES (LEFT(@email,@i-1), SUBSTRING(@email,@i+1,20))
    ELSE
        INSERT INTO @t VALUES (NULL,NULL)

    RETURN 
END


--UDF Usage
SELECT 
    emails.ID, s.Username, s.Domain
FROM 
    emails
CROSS APPLY 
EmailParse(emails.EmailAddress) s

Upvotes: 14

Abe Miessler
Abe Miessler

Reputation: 85046

I would probably create a table UDF. Something along the lines of this:

CREATE FUNCTION [owner].[function_name]
(
      @parm1 <datatpe> = <default>
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT <column1, column2, ...>
      FROM <table, view, etc.>
      WHERE <some condition applies>      -- optional clauses
)

more info here.

It might seem like kind of a waste if you are only returning one row, but I think your alternatives(xml, parsing values from string on the fly) would make things much harder .

Upvotes: 5

John Woo
John Woo

Reputation: 263703

You can't create a function that returns two values. What's the problem when you call the function twice?

i have found an article in TechNet which explains how to create a Table-Valued User-Defined Functions.

Upvotes: 0

Related Questions