How to get table valued functions in the database?

Question

how to get table valued functions in the database?

Syed Nayab Ali · Answer

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

srinivas P · Answer

Thank you very much Ali

Priya Linge · Answer

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 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.

How to get table valued functions in the database?

Answers (3)