CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Prasanna Kumar J
Prasanna Kumar J

Reputation: 1628

Return table valued function return table structure like my actual table using Sql Server 2008?

I have a table named tableincentive like below

create table tableincentive (entry_date date,ag_id int,us_id int,lo_id int,loin_id int,de_id int,dest_id int,incentive_amt decimal(18,2),le_id int,lion_id int,etc...)

And i have a table valued function named [dbo].[func_getdetails]

 Create FUNCTION [dbo].[func_getdetails]
    (
        -- Add the parameters for the function here
        @ason_date date
    )
    RETURNS 
    @tableincentive TABLE 
    (
        -- Add the column definitions for the TABLE variable here
        entry_date date,ag_id int,us_id int,lo_id int,loin_id int,de_id int,dest_id int,incentive_amt decimal(18,2),le_id int,lion_id int,etc...
    )
    AS
    BEGIN
    -- some long process then
    insert into @tableincentive 
    select * from tableincentive;
    end

My problem is the table tableincentive has 43 columns so i create a table valued function return tableincentive structure.I need return like my actual table. that means in function

     RETURNS 
            @tableincentive TABLE 
            (
                -- Add the column definitions for the TABLE variable here
                entry_date date,ag_id int,us_id int,lo_id int,loin_id int,de_id int,dest_id int,incentive_amt decimal(18,2),le_id int,lion_id int,etc...
            )
            AS
begin end

changed to

RETURNS  tableincentive
            AS
begin 
end

or 

  RETURNS  @tableincentive table like tableincentive
                AS
    begin 
    end

Upvotes: 1

Views: 2492

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

User-defined functions need static results. That means you have to define the return type while creating the function. So If you want to have a static result set, then probably you should opt for a stored procedure.

Upvotes: 1

Related Questions