User Defined function that returns Table example in SQL Server

knd

SOLVED CLOSED ACTIVE ANSWERED
User: knd
Posted: on Jan 23, 2016 03:36 AM
Forum: SQL Server
Answer: 1
Views: 3246

CREATE FUNCTION fn_CalculateListing(@Listing ListingColumn READONLY)    --(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE 
AS
BEGIN
    DECLARE @result TABLE
    (
        ID INT,
        Name VARCHAR(50)
    )

    INSERT INTO @result(ID,Name)
    SELECT ROWID, Name
    FROM @Listing

    RETURN @Listing
END

I have make use of table-inline parameter but gives error as detail :Query and here is Error

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Jan 25, 2016 06:23 AM
Modified: on Jan 25, 2016 07:53 AM
Report

Hi knd,

Refer the below article.

CREATE FUNCTION fn_CalculateListing()
RETURNS @Listing TABLE 
(
	ID INT NOT NULL,
	Name VARCHAR(50) NOT NULL
)
AS
BEGIN
    DECLARE @TempTable TABLE(ID INT,Name VARCHAR(50))
 
    INSERT INTO @TempTable(ID,Name)
    SELECT ROWID, Name
    FROM YourTable --Table Name from you are getting record
 
	INSERT INTO @Listing
	SELECT ID,Name FROM @TempTable 
	RETURN
END

Pass Table as Parameter (Table Valued Parameters) to Stored Procedure in SQL Server

User Defined function that returns Table example in SQL Server

Company

Explore

Follow us

Contact Us