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, im not sure how to return it as a table, any help much appreciated!
-
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
returnsclause that defines a table structure. You can insert anything into the table variable (@DateHierarchyin 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 <= @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 gole dorfier : Why do you think he needs this rather than a stored procedure?le dorfier : Why do you think he needs this rather than a stored procedure? Can you conclude that without any further questions?ConcernedOfTunbridgeWells : You can't actually embed a stored procedure into a query - the best you can do is to insert into a table from a record set selected by it. Also, he seems to be quite specifically asking for help on how to write a table valued function. -
Use this as a template
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@param1, sysname, @p1> <data_type_for_param1, , int>, <@param2, sysname, @p2> <data_type_for_param2, , char> ) RETURNS <@Table_Variable_Name, sysname, @Table_Var> TABLE ( -- Add the column definitions for the TABLE variable here <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, <Column_2, sysname, c2> <Data_Type_For_Column2, , int> ) AS BEGIN -- Fill the table variable with the rows for your result set RETURN END GOThat will define your function. Then you would just use it as any other table:
Select * from MyFunction(Param1, Param2, etc.) -
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
Joe : If i do "select * from MySproc" I get an error saying "invalid object name 'theSproc' " -
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 ENDEdit
Based on comments to this question here is my recommendation. You want 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].loginnameIn 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].loginnameAs you can see the UDF is a lot more concise easier to read, and probally performs a little bit better since your not using the secondary temporary table (performance is a complete guess on my part).
If your 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 your indexing your temp table there should be no issue, and you will be using the tempDb less since table variables are kept in memory.
le dorfier : Could be because he's describing a scenario where an SP is more appropriate than a UDF. He's asking which to use, and the SP is sufficient (and simpler and more cohesive and more portable).JoshBerke : Assuming he's actually not passing parameters to the function perhaps.JoshBerke : Hmm someone deleted my comment? weird...Joe : I thought that sproc would be best as well I'm using temp tables to build the table of data to return but when I i try "select * from TheSproc" i get a "invalid object name 'theSproc' "JoshBerke : You don't select from the proc, I know you can execute a proc and put the results in a temp table. Also use Table Variables instead of temp tables, there are cases for temp tables but typically a table variable is a better choice.JoshBerke : Also if you want this function to be reusable in multiple areas a function is better IMHO. That is if this function will be reused in many s-procs...otherwise you're procs will need to create a temp table to store the results from the procedure.Joe : what i'm trying to do is something like "select * from TheSproc s join AnotherTable a on a.id=s.id" or is what im trying to achive only doable with a table value function?
0 comments:
Post a Comment