Return a table from a User defined function

Question

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

Ackroydd · Accepted Answer · 2013-04-12 09:24:30Z

1

You need to concatenate the text parts like so:

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

answered Apr 12, 2013 at 9:24

Ackroydd

1,4801 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oliver · Accepted Answer · 2013-04-12 09:32:03Z

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.

Devart · Accepted Answer · 2013-04-12 09:24:44Z

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

Collectives™ on Stack Overflow

Return a table from a User defined function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related