How to get table valued functions in the database?

Hi Srinivas,

here is the syntax to create a Table Valued function
 

CREATE FUNCTION FunctionName()

RETURNS @TableName TABLE (Table's Columns)

AS

BEGIN

    RETURN;

END;

this is the simplest way to create a table value function.

I am also mention an example here which help you to understand it well.
----------------------------------------------------------------------
CREATE FUNCTION GetStates()

RETURNS @States TABLE

(

    ShortName nchar(2),

    LongName nvarchar(50)

)

AS

BEGIN

    INSERT INTO @States VALUES(N'DC', N'District of Columbia'),

                             (N'MD', N'Maryland'),

                             (N'PA', N'Pennsylvania'),

                             (N'VA', N'Virginia'),

                             (N'WV', N'West Virginia');

    RETURN;

END;

GO

------------------------------------------------------------------

when you call this function as 'select * from Getstates()'
it will return 2 column 'ShortName' and 'LongName' as result in the form table.

Just because it is a table-type, you can access one, some, or all of its fields.
select LongName from GetStates()

we can also use parameters in table valued functions if want then I can help u.

I think it will help u.

Thanks

Hi,

CREATE FUNCTION [dbo].[function_string_to_table]
(
    @string VARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data VARCHAR(256)
)
BEGIN

    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END

    RETURN

END

This way we can get the table valued function.

Hope this will help you.

Thanks.