We do this using sp_ExecuteSQL
DECLARE @strWhere nvarchar(MAX), @strSelect nvarchar(MAX), @strSQL nvarchar(MAX)
--
SELECT @strWhere = 'WHERE 1=1'
+ CASE WHEN @MyPositionParam IS NULL THEN '' ELSE ' AND [Position] = @MyPositionParam' END
+ CASE WHEN @MyDeptParam IS NULL THEN '' ELSE ' AND [Dept] = @MyDeptParam' END
...
SELECT @strSelect = 'SELECT ' + @UserColumnListParameter -- Presuming columns provided by end user from a picklist
--
SELECT @strSQL = @strSelect + ' FROM dbo.MyTable1 AS T1 JOIN dbo.MyTable2 AS T2 ON T2.SomeID = T1.SomeID
--
EXEC sp_ExecuteSQL @strSQL,
N'@MyPositionParam varchar(999), @MyDeptParam varchar(999)',
@MyPositionParam = @MyPositionParam,
@MyDeptParam = @MyDeptParam
The benefit of this is:
@MyPositionParam is past to sp_ExecuteSQL (and to the SProc that calls it, if that is relevant) as a parameter using a native SQL datatype. I don't have to worry about SQL Injection, or embedded Single Quotes, or illegal string-dates etc. mucking up the syntax
My WHERE clause only contains phrases that are relevant to the query. I don't have any:
WHERE (@MyPositionParam IS NULL OR [Position] = @MyPositionParam)
AND (@MyDeptParam IS NULL OR [Dept] = @MyDeptParam)
which will almost certainly result in a Table Scan. My relevant-column-specific WHERE clause will be optimal for SQL to choose appropriate indexes etc.
Using sp_ExecuteSQL like this will cause the Query Optimisers to cache the query plan for the exact contents of @strSQL. Because I have used
AND [Position] = @MyPositionParam
and in particular I have not used actual values like:
AND [Position] = 'FooBar'
then there is a good chance that some/many of the users' actual queries get cached - for sure the popular ones will.
For security I can wrap this is SProcs which the user has EXECUTE permission on. Still need permission on the underlying table for the dynamic SQL, but if that that is an issue you can have the SProc EXECUTE AS to impersonate a specific permission user/role.
(My only experience with sp_ExectueSQL is via SProcs, but I expect you can do some sort of EXECUTE AS direct from an APP if you want to execute the SQL direct without an SProc wrapper. The key is to use sp_ExecuteSQL to get reuse of cached query plans)
