CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
Nejthe
Nejthe

Reputation: 580

Return a table from a User defined function

CREATE FUNCTION dbo.ta_OTMultiplierstest (@id int)
RETURNS nvarchar (100)
AS
BEGIN
DECLARE @text nvarchar(100)
set @text = ( SELECT  CONVERT(nvarchar(5), b.Duration), CONVERT(nvarchar(5),b.Mon),     CONVERT(nvarchar(5), b.Tue), CONVERT(nvarchar(5), b.Wed),
            CONVERT(nvarchar(5), b.Thu), CONVERT(nvarchar(5), b.Fri), CONVERT(nvarchar(5), b.Sat), CONVERT(nvarchar(5), b.Sun),
            CONVERT(nvarchar(5), b.DayOff), CONVERT(nvarchar(5), b.Holiday), CONVERT(nvarchar(5), b.Yearly),
            CONVERT(nvarchar(5), b.Maternity), CONVERT(nvarchar(5), b.Other)

            FROM ta_GenPolOTMultiplier b  WHERE b.PolHistID = @id 
            Group By b.Duration)--, b.Mon, b.Tue, b.Wed, b.Thu, b.Fri, b.Sat, b.Sun, b.DayOff, b.Holiday, b.Yearly, b.Maternity, b.Other)

return @text
END

I'm getting this error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" I know that i can't return more than one field I just need to know how i can solve this ? I need to return this function in a stored procedure and get a table of all these values

I appreciate any help

Upvotes: 1

Views: 294

Answers (3)

Oliver
Oliver

Reputation: 3255

You can do this with a "single statement table-valued function", as follows:

CREATE FUNCTION dbo.ta_OTMultiplierstest (@id int)
RETURNS TABLE
AS
RETURN
    SELECT  
         CONVERT(nvarchar(5), b.Duration)
        , CONVERT(nvarchar(5),b.Mon)
        , CONVERT(nvarchar(5), b.Tue)
        , CONVERT(nvarchar(5), b.Wed)
        , CONVERT(nvarchar(5), b.Thu)
        , CONVERT(nvarchar(5), b.Fri)
        , CONVERT(nvarchar(5), b.Sat)
        , CONVERT(nvarchar(5), b.Sun)
        , CONVERT(nvarchar(5), b.DayOff)
        , CONVERT(nvarchar(5), b.Holiday)
        , CONVERT(nvarchar(5), b.Yearly)
        , CONVERT(nvarchar(5), b.Maternity)
        , CONVERT(nvarchar(5), b.Other)
    FROM dbo.ta_GenPolOTMultiplier b  WHERE b.PolHistID = @id 
    Group By b.Duration

This will get you a table you can use or join to in your procedure.

If you need a single, concatenated string, see Ackroydd's answer.

Upvotes: 1

Devart
Devart

Reputation: 121902

Try this one - 

CREATE FUNCTION dbo.ta_OTMultiplierstest (@id int)
RETURNS @Result TABLE 
(
    Duration nvarchar(5),
    Mon nvarchar(5),
    Tue nvarchar(5),
    Wed nvarchar(5),
    Thu nvarchar(5),
    Fri nvarchar(5),
    Sat nvarchar(5),
    Sun nvarchar(5),
    DayOff nvarchar(5),
    Holiday nvarchar(5),
    Yearly nvarchar(5),
    Maternity nvarchar(5),
    Other nvarchar(5)
) 
AS
BEGIN

    INSERT INTO @Result 
    (
        Duration, 
        Mon,
        Tue,
        Wed,
        Thu,
        Fri,
        Sat,
        Sun,
        DayOff,
        Holiday,
        Yearly,
        Maternity,
        Other
    ) 
    SELECT  
         CONVERT(nvarchar(5), b.Duration)
        , CONVERT(nvarchar(5),b.Mon)
        , CONVERT(nvarchar(5), b.Tue)
        , CONVERT(nvarchar(5), b.Wed)
        , CONVERT(nvarchar(5), b.Thu)
        , CONVERT(nvarchar(5), b.Fri)
        , CONVERT(nvarchar(5), b.Sat)
        , CONVERT(nvarchar(5), b.Sun)
        , CONVERT(nvarchar(5), b.DayOff)
        , CONVERT(nvarchar(5), b.Holiday)
        , CONVERT(nvarchar(5), b.Yearly)
        , CONVERT(nvarchar(5), b.Maternity)
        , CONVERT(nvarchar(5), b.Other)
    FROM dbo.ta_GenPolOTMultiplier b  WHERE b.PolHistID = @id 
    Group By b.Duration

    RETURN

END

Upvotes: 0

Ackroydd
Ackroydd

Reputation: 1610

You need to concatenate the text parts like so: 

set @text = ( SELECT  CONVERT(nvarchar(5), b.Duration) + CONVERT(nvarchar(5),b.Mon) ...

Upvotes: 1

Related Questions