tsql returning a table from a function or store procedure

Question

This is more of a syntax question I m trying to write a store procedure or function that I can embed into a query such as   select   from MyBigProcOrFunction   I m trying to define a tabular function but I do not understand how to do it as I build tmp tables to work out the data before I finally have the return at the endtable  My mark up for my code is   create function FnGetCompanyIdWithCategories   returns table as  return    select   into a  tempTable from stuff   etc   select companyid Company MarketSector from  tempTables  the returning table data     If I define a function  How do I return it as a table

User · Answer

Use this as a template  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author        lt Author  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               CREATE FUNCTION  lt Table Function Name  sysname  FunctionName gt         -- Add the parameters for the function here      lt  param1  sysname   p1 gt   lt data type for param1    int gt         lt  param2  sysname   p2 gt   lt data type for param2    char gt    RETURNS   lt  Table Variable Name  sysname   Table Var gt  TABLE        -- Add the column definitions for the TABLE variable here      lt Column 1  sysname  c1 gt   lt Data Type For Column1    int gt         lt Column 2  sysname  c2 gt   lt Data Type For Column2    int gt    AS BEGIN     -- Fill the table variable with the rows for your result set      RETURN  END GO   That will define your function  Then you would just use it as any other table   Select   from MyFunction Param1  Param2  etc

User · Answer

You don t need  shouldn t use  a function as far as I can tell  The stored procedure will return tabular data from any SELECT statements you include that return tabular data   A stored proc does not use RETURN statements    CREATE PROCEDURE name  AS   SELECT stuff INTO  temptbl1              SELECT columns FROM  temptbln

User · Answer

You can t access Temporary Tables from within a SQL Function  You will need to use table variables so essentially   ALTER FUNCTION FnGetCompanyIdWithCategories   RETURNS   rtnTable TABLE        -- columns returned by the function     ID UNIQUEIDENTIFIER NOT NULL      Name nvarchar 255  NOT NULL   AS BEGIN DECLARE  TempTable table  id uniqueidentifier  name nvarchar 255        insert into  myTable  select from your stuff  --This select returns data insert into  rtnTable SELECT ID  name FROM  mytable  return END   Edit  Based on comments to this question here is my recommendation  You want to join the results of either a procedure or table-valued function in another query  I will show you how you can do it then you pick the one you prefer  I am going to be using sample code from one of my schemas  but you should be able to adapt it  Both are viable solutions first with a stored procedure    declare  table as table  id int  name nvarchar 50  templateid int account nvarchar 50    insert into  table execute industry getall  select    from  table  inner join  user       on account  user  loginname   In this case  you have to declare a temporary table or table variable to store the results of the procedure  Now Let s look at how you would do this if you were using a UDF  select   from fn Industry GetAll   inner join  user       on account  user  loginname   As you can see the UDF is a lot more concise easier to read  and probably performs a little bit better since you re not using the secondary temporary table  performance is a complete guess on my part    If you re going to be reusing your function procedure in lots of other places  I think the UDF is your best choice  The only catch is you will have to stop using  Temp tables and use table variables  Unless you re indexing your temp table  there should be no issue  and you will be using the tempDb less since table variables are kept in memory

User · Answer

Use this as a template  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author        lt Author  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               CREATE FUNCTION  lt Table Function Name  sysname  FunctionName gt         -- Add the parameters for the function here      lt  param1  sysname   p1 gt   lt data type for param1    int gt         lt  param2  sysname   p2 gt   lt data type for param2    char gt    RETURNS   lt  Table Variable Name  sysname   Table Var gt  TABLE        -- Add the column definitions for the TABLE variable here      lt Column 1  sysname  c1 gt   lt Data Type For Column1    int gt         lt Column 2  sysname  c2 gt   lt Data Type For Column2    int gt    AS BEGIN     -- Fill the table variable with the rows for your result set      RETURN  END GO   That will define your function  Then you would just use it as any other table   Select   from MyFunction Param1  Param2  etc

User · Answer

You can t access Temporary Tables from within a SQL Function  You will need to use table variables so essentially   ALTER FUNCTION FnGetCompanyIdWithCategories   RETURNS   rtnTable TABLE        -- columns returned by the function     ID UNIQUEIDENTIFIER NOT NULL      Name nvarchar 255  NOT NULL   AS BEGIN DECLARE  TempTable table  id uniqueidentifier  name nvarchar 255        insert into  myTable  select from your stuff  --This select returns data insert into  rtnTable SELECT ID  name FROM  mytable  return END   Edit  Based on comments to this question here is my recommendation  You want to join the results of either a procedure or table-valued function in another query  I will show you how you can do it then you pick the one you prefer  I am going to be using sample code from one of my schemas  but you should be able to adapt it  Both are viable solutions first with a stored procedure    declare  table as table  id int  name nvarchar 50  templateid int account nvarchar 50    insert into  table execute industry getall  select    from  table  inner join  user       on account  user  loginname   In this case  you have to declare a temporary table or table variable to store the results of the procedure  Now Let s look at how you would do this if you were using a UDF  select   from fn Industry GetAll   inner join  user       on account  user  loginname   As you can see the UDF is a lot more concise easier to read  and probably performs a little bit better since you re not using the secondary temporary table  performance is a complete guess on my part    If you re going to be reusing your function procedure in lots of other places  I think the UDF is your best choice  The only catch is you will have to stop using  Temp tables and use table variables  Unless you re indexing your temp table  there should be no issue  and you will be using the tempDb less since table variables are kept in memory

User · Answer

You need a special type of function known as a table valued function   Below is a somewhat long-winded example that builds a date dimension for a data warehouse   Note the returns clause that defines a table structure   You can insert anything into the table variable   DateHierarchy in this case  that you want  including building a temporary table and copying the contents into it   if object id   ods uf DateHierarchy   is not null     drop function ods uf DateHierarchy go  create function ods uf DateHierarchy           DateFrom datetime         DateTo   datetime   returns  DateHierarchy table           DateKey           datetime         DisplayDate       varchar  20          SemanticDate      datetime         MonthKey          int              DisplayMonth      varchar  10          FirstDayOfMonth   datetime         QuarterKey        int         DisplayQuarter    varchar  10          FirstDayOfQuarter datetime         YearKey           int         DisplayYear       varchar  10          FirstDayOfYear    datetime   as begin     declare  year            int              quarter         int              month           int              day             int              m1ofqtr         int              DisplayDate     varchar  20               DisplayQuarter  varchar  10               DisplayMonth    varchar  10               DisplayYear     varchar  10               today           datetime              MonthKey        int              QuarterKey      int              YearKey         int              SemanticDate    datetime              FirstOfMonth    datetime              FirstOfQuarter  datetime              FirstOfYear     datetime              MStr            varchar  2               QStr            varchar  2               Ystr            varchar  4               DStr            varchar  2               DateStr         varchar  10        --     Previous                                                         -- Special placeholder date of 1 1 1800 used to denote  previous      -- so that naive date calculations sort and compare in a sensible     -- order      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             1800-01-01            Previous            1800-01-01           180001           Prev            1800-01-01           18001           Prev            1800-01-01           1800           Prev            1800-01-01             --     Calendar Dates                                                   -- These are generated from the date range specified in the input     -- parameters      --     set  today    Datefrom     while  today  lt    DateTo begin          set  year   datepart  yyyy   today          set  month   datepart  mm   today          set  day   datepart  dd   today          set  quarter   case when  month in  1 2 3  then 1                             when  month in  4 5 6  then 2                             when  month in  7 8 9  then 3                             when  month in  10 11 12  then 4                         end         set  m1ofqtr    quarter   3 - 2           set  DisplayDate   left  convert  varchar   today  113   11          set  SemanticDate    today         set  MonthKey    year   100    month         set  DisplayMonth   substring  convert  varchar   today  113   4  8          set  Mstr   right   0    convert  varchar   month   2          set  Dstr   right   0    convert  varchar   day   2          set  Ystr   convert  varchar   year          set  DateStr    Ystr    -     Mstr    -01          set  FirstOfMonth   convert  datetime   DateStr  120          set  QuarterKey    year   10    quarter         set  DisplayQuarter    Q    convert  varchar   quarter                                              convert  varchar   year          set  QStr   right   0    convert  varchar   m1ofqtr   2             set  DateStr    Ystr    -     Qstr    -01           set  FirstOfQuarter   convert  datetime   DateStr  120          set  YearKey    year         set  DisplayYear   convert  varchar   year          set  DateStr    Ystr    -01-01          set  FirstOfYear   convert  datetime   DateStr            insert  DateHierarchy                DateKey              DisplayDate              SemanticDate              MonthKey              DisplayMonth              FirstDayOfMonth              QuarterKey              DisplayQuarter              FirstDayOfQuarter              YearKey              DisplayYear              FirstDayOfYear           values                 today               DisplayDate               SemanticDate               Monthkey               DisplayMonth               FirstOfMonth               QuarterKey               DisplayQuarter               FirstOfQuarter               YearKey               DisplayYear               FirstOfYear                    set  today   dateadd  dd  1   today      end      --     Specials                                                         --  Ongoing    Error  and  Not Recorded  set two years apart to     -- avoid accidental collisions on  Next Year  calculations      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9000-01-01            Ongoing            9000-01-01           900001           Ong             9000-01-01           90001           Ong             9000-01-01           9000           Ong             9000-01-01             insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9100-01-01            Error           null          910001           Error           null          91001           Error           null          9100           Err           null            insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9200-01-01            Not Recorded           null          920001           N R           null          92001           N R           null          9200           N R           null            return end  go

User · Answer

You don t need  shouldn t use  a function as far as I can tell  The stored procedure will return tabular data from any SELECT statements you include that return tabular data   A stored proc does not use RETURN statements    CREATE PROCEDURE name  AS   SELECT stuff INTO  temptbl1              SELECT columns FROM  temptbln

User · Answer

You need a special type of function known as a table valued function   Below is a somewhat long-winded example that builds a date dimension for a data warehouse   Note the returns clause that defines a table structure   You can insert anything into the table variable   DateHierarchy in this case  that you want  including building a temporary table and copying the contents into it   if object id   ods uf DateHierarchy   is not null     drop function ods uf DateHierarchy go  create function ods uf DateHierarchy           DateFrom datetime         DateTo   datetime   returns  DateHierarchy table           DateKey           datetime         DisplayDate       varchar  20          SemanticDate      datetime         MonthKey          int              DisplayMonth      varchar  10          FirstDayOfMonth   datetime         QuarterKey        int         DisplayQuarter    varchar  10          FirstDayOfQuarter datetime         YearKey           int         DisplayYear       varchar  10          FirstDayOfYear    datetime   as begin     declare  year            int              quarter         int              month           int              day             int              m1ofqtr         int              DisplayDate     varchar  20               DisplayQuarter  varchar  10               DisplayMonth    varchar  10               DisplayYear     varchar  10               today           datetime              MonthKey        int              QuarterKey      int              YearKey         int              SemanticDate    datetime              FirstOfMonth    datetime              FirstOfQuarter  datetime              FirstOfYear     datetime              MStr            varchar  2               QStr            varchar  2               Ystr            varchar  4               DStr            varchar  2               DateStr         varchar  10        --     Previous                                                         -- Special placeholder date of 1 1 1800 used to denote  previous      -- so that naive date calculations sort and compare in a sensible     -- order      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             1800-01-01            Previous            1800-01-01           180001           Prev            1800-01-01           18001           Prev            1800-01-01           1800           Prev            1800-01-01             --     Calendar Dates                                                   -- These are generated from the date range specified in the input     -- parameters      --     set  today    Datefrom     while  today  lt    DateTo begin          set  year   datepart  yyyy   today          set  month   datepart  mm   today          set  day   datepart  dd   today          set  quarter   case when  month in  1 2 3  then 1                             when  month in  4 5 6  then 2                             when  month in  7 8 9  then 3                             when  month in  10 11 12  then 4                         end         set  m1ofqtr    quarter   3 - 2           set  DisplayDate   left  convert  varchar   today  113   11          set  SemanticDate    today         set  MonthKey    year   100    month         set  DisplayMonth   substring  convert  varchar   today  113   4  8          set  Mstr   right   0    convert  varchar   month   2          set  Dstr   right   0    convert  varchar   day   2          set  Ystr   convert  varchar   year          set  DateStr    Ystr    -     Mstr    -01          set  FirstOfMonth   convert  datetime   DateStr  120          set  QuarterKey    year   10    quarter         set  DisplayQuarter    Q    convert  varchar   quarter                                              convert  varchar   year          set  QStr   right   0    convert  varchar   m1ofqtr   2             set  DateStr    Ystr    -     Qstr    -01           set  FirstOfQuarter   convert  datetime   DateStr  120          set  YearKey    year         set  DisplayYear   convert  varchar   year          set  DateStr    Ystr    -01-01          set  FirstOfYear   convert  datetime   DateStr            insert  DateHierarchy                DateKey              DisplayDate              SemanticDate              MonthKey              DisplayMonth              FirstDayOfMonth              QuarterKey              DisplayQuarter              FirstDayOfQuarter              YearKey              DisplayYear              FirstDayOfYear           values                 today               DisplayDate               SemanticDate               Monthkey               DisplayMonth               FirstOfMonth               QuarterKey               DisplayQuarter               FirstOfQuarter               YearKey               DisplayYear               FirstOfYear                    set  today   dateadd  dd  1   today      end      --     Specials                                                         --  Ongoing    Error  and  Not Recorded  set two years apart to     -- avoid accidental collisions on  Next Year  calculations      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9000-01-01            Ongoing            9000-01-01           900001           Ong             9000-01-01           90001           Ong             9000-01-01           9000           Ong             9000-01-01             insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9100-01-01            Error           null          910001           Error           null          91001           Error           null          9100           Err           null            insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9200-01-01            Not Recorded           null          920001           N R           null          92001           N R           null          9200           N R           null            return end  go

User · Answer

You don t need  shouldn t use  a function as far as I can tell  The stored procedure will return tabular data from any SELECT statements you include that return tabular data   A stored proc does not use RETURN statements    CREATE PROCEDURE name  AS   SELECT stuff INTO  temptbl1              SELECT columns FROM  temptbln

User · Answer

You don t need  shouldn t use  a function as far as I can tell  The stored procedure will return tabular data from any SELECT statements you include that return tabular data   A stored proc does not use RETURN statements    CREATE PROCEDURE name  AS   SELECT stuff INTO  temptbl1              SELECT columns FROM  temptbln

User · Answer

You can t access Temporary Tables from within a SQL Function  You will need to use table variables so essentially   ALTER FUNCTION FnGetCompanyIdWithCategories   RETURNS   rtnTable TABLE        -- columns returned by the function     ID UNIQUEIDENTIFIER NOT NULL      Name nvarchar 255  NOT NULL   AS BEGIN DECLARE  TempTable table  id uniqueidentifier  name nvarchar 255        insert into  myTable  select from your stuff  --This select returns data insert into  rtnTable SELECT ID  name FROM  mytable  return END   Edit  Based on comments to this question here is my recommendation  You want to join the results of either a procedure or table-valued function in another query  I will show you how you can do it then you pick the one you prefer  I am going to be using sample code from one of my schemas  but you should be able to adapt it  Both are viable solutions first with a stored procedure    declare  table as table  id int  name nvarchar 50  templateid int account nvarchar 50    insert into  table execute industry getall  select    from  table  inner join  user       on account  user  loginname   In this case  you have to declare a temporary table or table variable to store the results of the procedure  Now Let s look at how you would do this if you were using a UDF  select   from fn Industry GetAll   inner join  user       on account  user  loginname   As you can see the UDF is a lot more concise easier to read  and probably performs a little bit better since you re not using the secondary temporary table  performance is a complete guess on my part    If you re going to be reusing your function procedure in lots of other places  I think the UDF is your best choice  The only catch is you will have to stop using  Temp tables and use table variables  Unless you re indexing your temp table  there should be no issue  and you will be using the tempDb less since table variables are kept in memory

User · Answer

You need a special type of function known as a table valued function   Below is a somewhat long-winded example that builds a date dimension for a data warehouse   Note the returns clause that defines a table structure   You can insert anything into the table variable   DateHierarchy in this case  that you want  including building a temporary table and copying the contents into it   if object id   ods uf DateHierarchy   is not null     drop function ods uf DateHierarchy go  create function ods uf DateHierarchy           DateFrom datetime         DateTo   datetime   returns  DateHierarchy table           DateKey           datetime         DisplayDate       varchar  20          SemanticDate      datetime         MonthKey          int              DisplayMonth      varchar  10          FirstDayOfMonth   datetime         QuarterKey        int         DisplayQuarter    varchar  10          FirstDayOfQuarter datetime         YearKey           int         DisplayYear       varchar  10          FirstDayOfYear    datetime   as begin     declare  year            int              quarter         int              month           int              day             int              m1ofqtr         int              DisplayDate     varchar  20               DisplayQuarter  varchar  10               DisplayMonth    varchar  10               DisplayYear     varchar  10               today           datetime              MonthKey        int              QuarterKey      int              YearKey         int              SemanticDate    datetime              FirstOfMonth    datetime              FirstOfQuarter  datetime              FirstOfYear     datetime              MStr            varchar  2               QStr            varchar  2               Ystr            varchar  4               DStr            varchar  2               DateStr         varchar  10        --     Previous                                                         -- Special placeholder date of 1 1 1800 used to denote  previous      -- so that naive date calculations sort and compare in a sensible     -- order      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             1800-01-01            Previous            1800-01-01           180001           Prev            1800-01-01           18001           Prev            1800-01-01           1800           Prev            1800-01-01             --     Calendar Dates                                                   -- These are generated from the date range specified in the input     -- parameters      --     set  today    Datefrom     while  today  lt    DateTo begin          set  year   datepart  yyyy   today          set  month   datepart  mm   today          set  day   datepart  dd   today          set  quarter   case when  month in  1 2 3  then 1                             when  month in  4 5 6  then 2                             when  month in  7 8 9  then 3                             when  month in  10 11 12  then 4                         end         set  m1ofqtr    quarter   3 - 2           set  DisplayDate   left  convert  varchar   today  113   11          set  SemanticDate    today         set  MonthKey    year   100    month         set  DisplayMonth   substring  convert  varchar   today  113   4  8          set  Mstr   right   0    convert  varchar   month   2          set  Dstr   right   0    convert  varchar   day   2          set  Ystr   convert  varchar   year          set  DateStr    Ystr    -     Mstr    -01          set  FirstOfMonth   convert  datetime   DateStr  120          set  QuarterKey    year   10    quarter         set  DisplayQuarter    Q    convert  varchar   quarter                                              convert  varchar   year          set  QStr   right   0    convert  varchar   m1ofqtr   2             set  DateStr    Ystr    -     Qstr    -01           set  FirstOfQuarter   convert  datetime   DateStr  120          set  YearKey    year         set  DisplayYear   convert  varchar   year          set  DateStr    Ystr    -01-01          set  FirstOfYear   convert  datetime   DateStr            insert  DateHierarchy                DateKey              DisplayDate              SemanticDate              MonthKey              DisplayMonth              FirstDayOfMonth              QuarterKey              DisplayQuarter              FirstDayOfQuarter              YearKey              DisplayYear              FirstDayOfYear           values                 today               DisplayDate               SemanticDate               Monthkey               DisplayMonth               FirstOfMonth               QuarterKey               DisplayQuarter               FirstOfQuarter               YearKey               DisplayYear               FirstOfYear                    set  today   dateadd  dd  1   today      end      --     Specials                                                         --  Ongoing    Error  and  Not Recorded  set two years apart to     -- avoid accidental collisions on  Next Year  calculations      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9000-01-01            Ongoing            9000-01-01           900001           Ong             9000-01-01           90001           Ong             9000-01-01           9000           Ong             9000-01-01             insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9100-01-01            Error           null          910001           Error           null          91001           Error           null          9100           Err           null            insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9200-01-01            Not Recorded           null          920001           N R           null          92001           N R           null          9200           N R           null            return end  go

User · Answer

Use this as a template  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author        lt Author  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               CREATE FUNCTION  lt Table Function Name  sysname  FunctionName gt         -- Add the parameters for the function here      lt  param1  sysname   p1 gt   lt data type for param1    int gt         lt  param2  sysname   p2 gt   lt data type for param2    char gt    RETURNS   lt  Table Variable Name  sysname   Table Var gt  TABLE        -- Add the column definitions for the TABLE variable here      lt Column 1  sysname  c1 gt   lt Data Type For Column1    int gt         lt Column 2  sysname  c2 gt   lt Data Type For Column2    int gt    AS BEGIN     -- Fill the table variable with the rows for your result set      RETURN  END GO   That will define your function  Then you would just use it as any other table   Select   from MyFunction Param1  Param2  etc

User · Answer

Use this as a template  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author        lt Author  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               CREATE FUNCTION  lt Table Function Name  sysname  FunctionName gt         -- Add the parameters for the function here      lt  param1  sysname   p1 gt   lt data type for param1    int gt         lt  param2  sysname   p2 gt   lt data type for param2    char gt    RETURNS   lt  Table Variable Name  sysname   Table Var gt  TABLE        -- Add the column definitions for the TABLE variable here      lt Column 1  sysname  c1 gt   lt Data Type For Column1    int gt         lt Column 2  sysname  c2 gt   lt Data Type For Column2    int gt    AS BEGIN     -- Fill the table variable with the rows for your result set      RETURN  END GO   That will define your function  Then you would just use it as any other table   Select   from MyFunction Param1  Param2  etc

User · Answer

You need a special type of function known as a table valued function   Below is a somewhat long-winded example that builds a date dimension for a data warehouse   Note the returns clause that defines a table structure   You can insert anything into the table variable   DateHierarchy in this case  that you want  including building a temporary table and copying the contents into it   if object id   ods uf DateHierarchy   is not null     drop function ods uf DateHierarchy go  create function ods uf DateHierarchy           DateFrom datetime         DateTo   datetime   returns  DateHierarchy table           DateKey           datetime         DisplayDate       varchar  20          SemanticDate      datetime         MonthKey          int              DisplayMonth      varchar  10          FirstDayOfMonth   datetime         QuarterKey        int         DisplayQuarter    varchar  10          FirstDayOfQuarter datetime         YearKey           int         DisplayYear       varchar  10          FirstDayOfYear    datetime   as begin     declare  year            int              quarter         int              month           int              day             int              m1ofqtr         int              DisplayDate     varchar  20               DisplayQuarter  varchar  10               DisplayMonth    varchar  10               DisplayYear     varchar  10               today           datetime              MonthKey        int              QuarterKey      int              YearKey         int              SemanticDate    datetime              FirstOfMonth    datetime              FirstOfQuarter  datetime              FirstOfYear     datetime              MStr            varchar  2               QStr            varchar  2               Ystr            varchar  4               DStr            varchar  2               DateStr         varchar  10        --     Previous                                                         -- Special placeholder date of 1 1 1800 used to denote  previous      -- so that naive date calculations sort and compare in a sensible     -- order      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             1800-01-01            Previous            1800-01-01           180001           Prev            1800-01-01           18001           Prev            1800-01-01           1800           Prev            1800-01-01             --     Calendar Dates                                                   -- These are generated from the date range specified in the input     -- parameters      --     set  today    Datefrom     while  today  lt    DateTo begin          set  year   datepart  yyyy   today          set  month   datepart  mm   today          set  day   datepart  dd   today          set  quarter   case when  month in  1 2 3  then 1                             when  month in  4 5 6  then 2                             when  month in  7 8 9  then 3                             when  month in  10 11 12  then 4                         end         set  m1ofqtr    quarter   3 - 2           set  DisplayDate   left  convert  varchar   today  113   11          set  SemanticDate    today         set  MonthKey    year   100    month         set  DisplayMonth   substring  convert  varchar   today  113   4  8          set  Mstr   right   0    convert  varchar   month   2          set  Dstr   right   0    convert  varchar   day   2          set  Ystr   convert  varchar   year          set  DateStr    Ystr    -     Mstr    -01          set  FirstOfMonth   convert  datetime   DateStr  120          set  QuarterKey    year   10    quarter         set  DisplayQuarter    Q    convert  varchar   quarter                                              convert  varchar   year          set  QStr   right   0    convert  varchar   m1ofqtr   2             set  DateStr    Ystr    -     Qstr    -01           set  FirstOfQuarter   convert  datetime   DateStr  120          set  YearKey    year         set  DisplayYear   convert  varchar   year          set  DateStr    Ystr    -01-01          set  FirstOfYear   convert  datetime   DateStr            insert  DateHierarchy                DateKey              DisplayDate              SemanticDate              MonthKey              DisplayMonth              FirstDayOfMonth              QuarterKey              DisplayQuarter              FirstDayOfQuarter              YearKey              DisplayYear              FirstDayOfYear           values                 today               DisplayDate               SemanticDate               Monthkey               DisplayMonth               FirstOfMonth               QuarterKey               DisplayQuarter               FirstOfQuarter               YearKey               DisplayYear               FirstOfYear                    set  today   dateadd  dd  1   today      end      --     Specials                                                         --  Ongoing    Error  and  Not Recorded  set two years apart to     -- avoid accidental collisions on  Next Year  calculations      --     insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9000-01-01            Ongoing            9000-01-01           900001           Ong             9000-01-01           90001           Ong             9000-01-01           9000           Ong             9000-01-01             insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9100-01-01            Error           null          910001           Error           null          91001           Error           null          9100           Err           null            insert  DateHierarchy            DateKey          DisplayDate          SemanticDate          MonthKey          DisplayMonth          FirstDayOfMonth          QuarterKey          DisplayQuarter          FirstDayOfQuarter          YearKey          DisplayYear          FirstDayOfYear       values             9200-01-01            Not Recorded           null          920001           N R           null          92001           N R           null          9200           N R           null            return end  go

User · Answer

You can t access Temporary Tables from within a SQL Function  You will need to use table variables so essentially   ALTER FUNCTION FnGetCompanyIdWithCategories   RETURNS   rtnTable TABLE        -- columns returned by the function     ID UNIQUEIDENTIFIER NOT NULL      Name nvarchar 255  NOT NULL   AS BEGIN DECLARE  TempTable table  id uniqueidentifier  name nvarchar 255        insert into  myTable  select from your stuff  --This select returns data insert into  rtnTable SELECT ID  name FROM  mytable  return END   Edit  Based on comments to this question here is my recommendation  You want to join the results of either a procedure or table-valued function in another query  I will show you how you can do it then you pick the one you prefer  I am going to be using sample code from one of my schemas  but you should be able to adapt it  Both are viable solutions first with a stored procedure    declare  table as table  id int  name nvarchar 50  templateid int account nvarchar 50    insert into  table execute industry getall  select    from  table  inner join  user       on account  user  loginname   In this case  you have to declare a temporary table or table variable to store the results of the procedure  Now Let s look at how you would do this if you were using a UDF  select   from fn Industry GetAll   inner join  user       on account  user  loginname   As you can see the UDF is a lot more concise easier to read  and probably performs a little bit better since you re not using the secondary temporary table  performance is a complete guess on my part    If you re going to be reusing your function procedure in lots of other places  I think the UDF is your best choice  The only catch is you will have to stop using  Temp tables and use table variables  Unless you re indexing your temp table  there should be no issue  and you will be using the tempDb less since table variables are kept in memory

[sql-server] tsql returning a table from a function or store procedure

Examples related to sql-server

Examples related to tsql