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.