CODEWITHSUNDEEP
sqlsql-serversql-server-2008function
John Bustos
John Bustos

Reputation: 19574

SQL Server Create Function With Variable Column For Aggregation / Output

Suppose I have the following 2 tables in my SQL Server (2008) DB:

Table1:

Col1:    ...   Map:
1        ...   a
2        ...   a
3        ...   b
4        ...   c
5        ...   c

Mapper:

Map:     Out1:    Out2: ...
a        ab       ac
b        ab       bd
c        cd       ac
d        cd       bd   
    ... (whatever) ...

Now, I would like to create a table valued function that would return something along the lines of the following:

SELECT 
   M.Out1, 
   SUM(T.Col1)
FROM
   Table1 as T,
   Mapper as M
WHERE
   T.Map = M.Map
GROUP BY
   M.Out1

HOWEVER I would like to be able to include a parameter which can dictate which column of Mapper to Return / Group By (ie, Not be limited to Out1, but also be able to return the query grouped by possibly Map or Out2, etc based upon some form of user input (I would assume typing in the col. name as the defining input)).

I've seen posts that say you can do this using dynamic SQL, but warn of SQL Injection... Is there a better way??

Thanks!!

Upvotes: 0

Views: 77

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Here is an example loosely based on your example. Notice I also changed your join to the ANSI-92 style join instead of the older ANSI-89 style join. This is easier to read and less prone to accidental cross joins.

declare @ColumnNameParameter sysname = 'MyColumn;] that is injection safe'

declare @SQL nvarchar(max)

set @SQL = 
'select M.' + QUOTENAME(@ColumnNameParameter) 
+ ', SUM(T.Col1)
from Table1 as T
join Mapper as M on M.Map = t.Map
GROUP BY M.' + QUOTENAME(@ColumnNameParameter) 

select @SQL

Upvotes: 2

RyanH
RyanH

Reputation: 141

You have two options that I can tell:

1) Use sp_executesql and store the results into a temp table that is also dynamically created. In this instance you should be able to run a 'SELECT [custom column mapping columns] INTO #TEMPTable FROM [Mapped/Joined Table[s]]'

2) Use CLR from within SQL.

Sorry, I noticed I didn't respond to your concern about SQL Injection. It's always something to be aware of and if you're worried about it then you can parse the user-input before adding it into the statement. For instance, you can make sure there is only one word present, doesn't extend past a particular length, doesn't contain any SQL keywords such as SELECT, UPDATE, DELETE, DROP, etc.

Upvotes: 0

Related Questions