June 5, 2019 at 9:14 am
Hi all,
I am currently extracting a lot of bi-temporal versioned tables from a core DWH at a given snapshot date. Simple queries become a lot of code as I have to filter in both technical and business dates.
So, I have read about table valued functions which returns table objects and have no negative impact on query performance.
However, I didn't find a solution to pass a table and column objects to the function: I hoped that something like that would work:
CREATE FUNCTION getTableSnapShot
(
@table TABLE,
@col COLUMN,
@dts SMALLDATETIME
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM @table
WHERE @col = <filtercriteria>
I cannot define the table as NVARCHAR, as it is a object in the query. I cannot define table datatypes as every table has different specifications.
Is there an elegant way to solve this?
Thanks
June 5, 2019 at 10:47 am
Table value functions have quite a few restrictions and can certainly affect performance if used incorrectly.
you could try using a table value parameter instead of @table, but you would have to create a "type" that corresponds to each table you will ever want to pass into the function.
personally I think this is not a viable solution.
rather than using a function, why not use dynamic sql (also not good) in your main code
declare @sql nvarchar(1000)
set @sql='select * from '+@table+' where '+@colum ='+@filtercode
sp_executesql @sql
might not work for your requirements as I can't see what you are working with.. but I think the table function is a no go
MVDBA
June 5, 2019 at 11:01 am
rather than using a function, why not use dynamic sql (also not good) in your main code
declare @sql nvarchar(1000)
set @sql='select * from '+@table+' where '+@colum ='+@filtercode
sp_executesql @sqlmight not work for your requirements as I can't see what you are working with.. but I think the table function is a no go
What you have there won't work (as it's missing the EXEC), and also (if it did) is very dangerous. You should never inject raw values into a dynamic statement. @filtercode should be parametrised and @table and @colum (it's spelt column by the way) should be quoted properly by using QUOTENAME. I wrote an article recently on Dynamic SQL which discusses these sorts of things, so if you want to understand more it should help you: Dos and Don'ts of Dynamic SQL.
To the OP: Is @table meant to be a table variable here, or the name of a table in the database? If the former, does it always have the same definition, or will it vary? If the latter, then you aren't going to be able to pass the object to a function (or even an SP) as the parameters for an object need to be defined.
If @table is the name of an object, you cannot do this with a function as what you are after would require Dynamic SQL (do not use the example above, it is not safe). Functions cannot make use of dynamic sql, as they cannot perform DDL or DML actions (apart from against variables). An EXECstatement might perform those actions so are not permitted. As a result you would have to use a Stored procedure if you need to go down the Dynamic SQL route.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 5, 2019 at 11:12 am
Thanks for the quick input.
@table
is meant to be an object in my example. I just wanted something like in MS Access, where I can input RecordSet objects as arguments and return them, too. I wished T-SQL would allow more non-primitive data types in that fashion to avoid auto-generated code which is then executed.
As I want to use the solution very simple as inline in my current queries I have to stick to writing everything down :-).
Thanks again for all your input!
June 5, 2019 at 11:25 am
Tables can be declared in SQL Server, but you need to be specific. You can't just do something like this:
DECLARE @Table table;
INSERT INTO @Table
SELECT *
FROM dbo.MyTable MT
WHERE MyColumn LIKE 'A%';
And expect @Table to have the same definition as MyTable. You need to define your objects. So you would have something like:
DECLARE @Table table (Id int,
MyColumn varchar(100));
INSERT INTO @Table (Id, MyColumn)
SELECT ID,
MyColumn
FROM dbo.MyTable
WHERE MyColumn LIKE 'A%';
For parameters, you need to define what is call a "TYPE", or more specifically a Table Type:
CREATE TYPE dbo.TableVar AS table (ID int,
MyColumn varchar(100));
Then you can reference that in the parameters of a function or SP as below:
CREATE PROC dbo.MyProc @SomeParam int, @table dbo.TableVar READONLY AS
BEGIN ...
...
END;
Note that a table parameter must be declared as READONLY though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 5, 2019 at 8:53 pm
You could do this using a stored procedure. The structure can be returned by the query itself, you don't have to pre-define it. In theory there are potential SQL injection issues, so be sure to limit access to this stored proc to only very limited personnel (such as you!).
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.getTableSnapShot
(
@table nvarchar(128),
@col nvarchar(128),
@dts smalldatetime,
@result nvarchar(4000) = NULL,
@result_exists bit = 0,
@print_sql bit = 0,
@exec_sql bit = 1
)
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = CASE WHEN @result_exists = 1 THEN
'INSERT INTO ' + @result + ' '
ELSE '' END +
'SELECT * ' + CASE WHEN @result IS NOT NULL AND @result_exists = 0 THEN
'INTO ' + @result + ' '
ELSE '' END +
'FROM [' + ISNULL(PARSENAME(@table, 2), 'dbo') + '].[' +
PARSENAME(@table, 1) + '] ' +
'WHERE [' + @col + '] = ''' + CONVERT(varchar(30), @dts, 121) + ''''
IF @print_sql = 1
PRINT @sql
IF @exec_sql = 1
EXEC(@sql)
GO
DECLARE @dts smalldatetime
SET @dts = GETDATE()
EXEC dbo.getTableSnapShot 'tab1','col1',@dts,@print_sql=1,@exec_sql=0
EXEC dbo.getTableSnapShot 'tab1','col1',@dts,'tempdb.dbo.tab1',@print_sql=1,@exec_sql=0
EXEC dbo.getTableSnapShot 'tabl','col1',@dts,'tempdb.dbo.tabl',1,@print_sql=1,@exec_sql=0
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply