October 21, 2011 at 10:02 am
I'm working in reporting, very much on the fringes of SQL managment, so be gentle with me. 😛
For much of our reporting, we have to find the latest record for a number of tables (in terms of a date field, not the order in which they are entered), grouped by a particular identifyer, on a particular date. Intially, we were looking to create some table-valued functions to do this for each record, but the number of tables we were doing this with grew. I therefore decided to start playing with the idea of a single table-valued function where one of the parameters would be the table.
Following this approach, I've managed to produce the following function and code:
CCC_AsAt(@table nvarchar(255), @GroupField nvarchar(255), @ConditionField nvarchar(255), @ReportDate nvarchar(255))
Where
@table is the table
@GroupField is a unique identifyer (e.g. customer number)
@ConditionField is a date field, where you want to find the max date
@ReportDate is the date you want to run the report
DECLARE @statement as nvarchar(max)
SET @statement = '
SELECT Main.*
FROM ' + @table + ' AS Main
INNER JOIN
(SELECT ' + @GroupField + ' AS GroupField,
MAX(' + @ConditionField + ') AS MaxField
FROM ' + @table + ' AS Max
WHERE (' + @ConditionField + ' <= CONVERT(DATETIME, £$£' + @ReportDate + '£$£, 102))
GROUP BY ' + @GroupField + ') AS JoinMax
ON Main.' + @GroupField + ' = JoinMax.GroupField
AND Main.' + @ConditionField + ' = JoinMax.MaxField'
-- Used £$£ as could not put an escaped single-quote in a single-quote
-- Will now replace the temporary use of £$£ with escaped single-quote
SET @statement = Replace(@statement,'£$£','''')
/* This is an example output of the query we have now constructed
SELECT Main.*
FROM SomeTable AS Main
INNER JOIN
(SELECT someKy AS GroupField,
MAX(StartDate) AS MaxField
FROM SomeTable AS Max
WHERE (StartDate <= CONVERT(DATETIME, '2011-09-30', 102))
GROUP BY someKy) AS JoinMax
ON Main.someKy = JoinMax.GroupField
AND Main.StartDate = JoinMax.MaxField
*/
-- Can now execute string as query
EXECUTE sp_executesql @statement
This works, but I'm struggling to turn it into a 'create function' script (i.e. what to put before and after the code I have written). I think that my main problem is becuase I cannot define the table the script creates (because the table is defined by the input and will be different each time).
Can anyone fill in the balnks or point me in the right direction please?
Thanks
Stuart
October 21, 2011 at 10:13 am
You can't do this in a function. Dynamic sql is not allowed in a function.
http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/[/url]
Also to use a table value parameter you have to define the table as a User-Defined Table Type. This takes the dynamic ability right of your equation. Each table would need to have a type defined, which means you would have to have a separate function for each table.
You could however do this in a stored procedure (assuming you leave off the TVP).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2011 at 11:27 am
I'll second what Sean wrote. I'll add that be careful about trying to do this with just one function/proc. You might lose some benefits from compiled plans, and might end up limiting yourself. Have to test and examine your environment to know, but in general I don't think it's worth trying to write one general proc here.
I'd suggest you write code that generates these easily for each table. It isn't that much work, and having the extra objects doesn't necessarily cause any issues. Plus if something changes in all tables, you just re-run your code to re-gen the procs.
October 24, 2011 at 2:25 am
Thanks for the views. I guess that I will make them individually then.
Thanks
Stuart
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply