September 21, 2015 at 3:11 pm
Jason A. Long (9/21/2015)
I think it depends a lot on what the SQL is being used for...Personally I write a lot of report procs that can have a several optional/multi-valued parameters. For those, dynamic sql is my weapon of choice.
Yes, using the OPTION(RECOMPILE) is good for handling optional parameters and a splitter function will take care of the multi-valued arrays that SSRS supplies, but, at a certain point it just makes sense to use dynamic sql... Especially when you have a large number of tables joined for the sole purpose providing a filter column.
I have ran into times where I thought it was needed, but I ended up going around it to where I didn't.
That said, if anyone knows a good reference for dynamic SQL to learn, please let me know. I would love to explore it more.
September 21, 2015 at 4:21 pm
xsevensinzx (9/21/2015)
Jason A. Long (9/21/2015)
I think it depends a lot on what the SQL is being used for...Personally I write a lot of report procs that can have a several optional/multi-valued parameters. For those, dynamic sql is my weapon of choice.
Yes, using the OPTION(RECOMPILE) is good for handling optional parameters and a splitter function will take care of the multi-valued arrays that SSRS supplies, but, at a certain point it just makes sense to use dynamic sql... Especially when you have a large number of tables joined for the sole purpose providing a filter column.
I have ran into times where I thought it was needed, but I ended up going around it to where I didn't.
That said, if anyone knows a good reference for dynamic SQL to learn, please let me know. I would love to explore it more.
The link provided by John (2nd post in this thread) is, IMO, about as good as it gets. Erland Sommarskog is extraordinary knowledgeable and provides a very unbiased presentation on the topic.
As far as "needed"... I suppose that depends on you feel like you need... Me personally, I "need" my reports to render as quickly as possible. Typically that means having the ability to reuse execution plans when a default set of parameter values is selected and not suffer from parameter sniffing when the defaults aren't used. I also want the code to be easy to read & maintain. So, for me, dynamic sql tends to be a good fit for reports with multiple multi-valued parameters.
Basically, dynamic sql is just another tool in the tool box. Like any other tool, it's appropriate for some jobs and not for others. I'm not going to bend over backwards to avoid it if I feels it's the right tool for the job.
September 22, 2015 at 7:01 am
Jason A. Long (9/21/2015)
xsevensinzx (9/21/2015)
Jason A. Long (9/21/2015)
I think it depends a lot on what the SQL is being used for...Personally I write a lot of report procs that can have a several optional/multi-valued parameters. For those, dynamic sql is my weapon of choice.
Yes, using the OPTION(RECOMPILE) is good for handling optional parameters and a splitter function will take care of the multi-valued arrays that SSRS supplies, but, at a certain point it just makes sense to use dynamic sql... Especially when you have a large number of tables joined for the sole purpose providing a filter column.
I have ran into times where I thought it was needed, but I ended up going around it to where I didn't.
That said, if anyone knows a good reference for dynamic SQL to learn, please let me know. I would love to explore it more.
The link provided by John (2nd post in this thread) is, IMO, about as good as it gets. Erland Sommarskog is extraordinary knowledgeable and provides a very unbiased presentation on the topic.
As far as "needed"... I suppose that depends on you feel like you need... Me personally, I "need" my reports to render as quickly as possible. Typically that means having the ability to reuse execution plans when a default set of parameter values is selected and not suffer from parameter sniffing when the defaults aren't used. I also want the code to be easy to read & maintain. So, for me, dynamic sql tends to be a good fit for reports with multiple multi-valued parameters.
Basically, dynamic sql is just another tool in the tool box. Like any other tool, it's appropriate for some jobs and not for others. I'm not going to bend over backwards to avoid it if I feels it's the right tool for the job.
Can you give a good example of some of the multi-valued parameters that you have used dynamic SQL for? I mean, I have plenty of multi-valued parameter reports that do not use dynamic SQL. So, maybe I'm missing the connection fully.
September 22, 2015 at 7:49 am
Can you give a good example of some of the multi-valued parameters that you have used dynamic SQL for? I mean, I have plenty of multi-valued parameter reports that do not use dynamic SQL. So, maybe I'm missing the connection fully.
Here's one that I build yesterday. It's purpose is to supply pick list values to report parameters. There are two reasons I chose to use dynamic sql.
1) The fact that it will need to provide data to both multi-valued and single valued parameters... In multi-value mode I want it to choose the top 999 values based on volume over the last year... In single value mode, I want it to pull all available values AND I wan't an "All Branches" option added to the list.
2) It's need's to be able to proved "cascading" values... If 1 or more Company_IDs are supplied, it needs to limit the returned branches based on those IDs. If no Company_IDs are supplied, it needs to ignore what section of the where clause.
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Rep_ActiveProviderBranches2_MV_GET
/* ==============================================================================
09/21/2015 JL, Created as a more efficient replacement for Rep_ActiveProviderBranches_MV_GET
============================================================================== */
/*
EXEC dbo.Rep_ActiveProviderBranches2_MV_GET
@IsMultiValue = 1,
@ProviderCompID = '252,278,279,281',
@Debug = 1
*/
@IsMultiValue BIT = 0,-- 0=No, 1=Yes
@ProviderCompID VARCHAR(MAX) = '0',
@Debug BIT = 0
AS
BEGIN
SET NOCOUNT ON;
SET @ProviderCompID = dbo.Global_GetSanitizedValue(@ProviderCompID);-- Global_GetSanitizedValue is a function that scrubs char & varchar parameter values
-- The purpose is to eliminate verbiage that cold be use for SQL injection w/o the need for sp_executesql
DECLARE @sql VARCHAR(MAX) =
CASE WHEN @IsMultiValue = 1 THEN '' ELSE '
SELECT
0 AS Branch_ID,
''All Branches'' AS BranchName
UNION ALL'
END + '
SELECT
b3.Branch_ID,
CONCAT(LTRIM(b3.Name), '' ('', b3.PhysCity, '', '', b3.PhysState, '')'') AS BranchName' +
CASE
WHEN @IsMultiValue = 0 THEN '
FROM
dbo.ContactBranch b3
WHERE
b3.Active = ''Y''
AND b3.LK_ContactTypeID = 3' +
CASE WHEN @ProviderCompID = '0' THEN '' ELSE '
AND b3.Company_ID IN (' + @ProviderCompID + ')
ORDER BY
BranchName'
END
ELSE '
FROM (
SELECT TOP 999
br.Branch_ID,
br.Name,
br.PhysCity,
br.PhysState
FROM dbo.Referral r
JOIN dbo.ContactBranch br
ON r.Provider_ID = br.Branch_ID
WHERE
br.LK_ContactTypeID = 3
AND br.Active = ''Y''' +
CASE WHEN @ProviderCompID = '0' THEN '' ELSE '
AND br.Company_ID IN (' + @ProviderCompID + ')'
END + '
AND r.CreatedDate BETWEEN DATEADD(yy, -1, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY
br.Branch_ID,
br.Name,
br.PhysCity,
br.PhysState
ORDER BY
COUNT(*) DESC,
br.Name
) b3'
END;
IF @Debug = 1
BEGIN
EXEC dbo.LongPrint @sql;-- LongPrint is a proc that allows for the printing of more characters than the standard PRINT command
END;
ELSE
BEGIN
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
Branch_ID INT,
BranchName VARCHAR(255)
);
INSERT #temp (Branch_ID,BranchName)
EXEC (@sql);
SELECT t.Branch_ID, t.BranchName
FROM #temp t
ORDER BY CASE WHEN t.Branch_ID = 0 THEN NULL ELSE t.BranchName END;
DROP TABLE #temp;
END;
END;
GO
Also note that I include an @DeBug parameter. This allows the proc to print the the generated sql for debugging purposes or execute it.
The use of the final #temp table simply allows it to play nice with SSRS and establish the sort order.
HTH,
Jason
September 22, 2015 at 8:53 am
Luis Cazares (9/21/2015)
WayneS (9/21/2015)
Kristen-173977 (9/20/2015)
GilaMonster (9/19/2015)
protecting against SQL Injection is done, in most cases, by ensuring that it is parameterised, except for the rare scenario where the user input can be white-listed.We parametrise for performance, and take the secondary (for us 🙂 ) benefit of protecting against SQL injection, but where we cannot parametrise dynamic SQL (table or column name substitution, for example) we just REPLACE any single-quote with a pair of single quotes.
Kristen,
Just confirming what I'm reading here... Do you actually have a production application with dynamic table / column names in the SQL?
I'm not sure about Kristen's case, but I had to deal with dynamic table names before. They created a weekly snapshot for a table appending the year and week to the name, so dynamic sql was needed to query historic data. I know that this could have been prevented with an additional date column in the table, but it was too late for this when I arrived and a code change for this wasn't a priority.
For this, I remember to use something like: QUOTENAME( 'MyTableName' + @YearAsChar4 + @WeekAsChar2) to prevent injection (even if no user input was allowed and strings where too short to be a real risk).
You could also have used dynamic SQL to create a view referring to the current and historical data, then subsequent code could use the view, in static SQL, rather than using dynamic SQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply