Parameterized dynamic SQL

One of the primary reasons for using parameterized dynamic SQL is to protect against SQL injection attacks. By separating the SQL query structure from the data, it ensures that user input is treated as data rather than executable code. This prevents attackers from injecting malicious SQL commands into the query. With parameterized dynamic SQL, the same query structure can be reused multiple times with different values. This makes the code more flexible, allowing for dynamic execution based on user input or varying conditions without altering the query structure each time. When using parameters, SQL engines can reuse execution plans for the same query structure with different values, as the structure (and thus the execution plan) remains the same. This reduces overhead from repeated parsing and compilation, leading to better performance.

CREATE OR ALTER PROCEDURE [dbo].[example_procedure]
(
    @OfficeOrGroup	CHAR(1) = NULL
  , @OfficeValue	CHAR(5) = NULL
  , @DeptOrGroup	CHAR(1) = NULL
  , @DeptValue	CHAR(5) = NULL
)
AS
BEGIN

   DECLARE @sql	NVARCHAR(MAX) = '';
   SET @sql += N'

   SELECT *
   FROM 
		[SAG_PROD].[dbo].[HBM_MATTER] AS [mtr] WITH(NOLOCK)
		INNER JOIN [SAG_PROD].[dbo].[HBL_OFFICE] AS [ofc] WITH(NOLOCK) ON [mtr].[offc] = [ofc].[code] 
		INNER JOIN [SAG_PROD].[dbo].[HBL_DEPT] AS [dpt] WITH(NOLOCK) ON [mtr].[dept] = [dpt].[id] 
		INNER JOIN [SAG_PROD].[dbo].[GLA_DEPT_GRP] AS [dpt_f] WITH(NOLOCK) ON [dpt].[id] = [dpt_f].[DEPT] 
		INNER JOIN [SAG_PROD].[dbo].[GLL_DEPTGR] AS [dpg_f] WITH(NOLOCK) ON [dpt_f].[GROUP_CODE] = [dpg_f].[GROUP_CODE]
		INNER JOIN [SAG_PROD].[dbo].[GLA_OFFC_GRP] AS [ofc_f] WITH(NOLOCK) ON [ofc].[CODE] = [ofc_f].[OFFC]
		INNER JOIN [SAG_PROD].[dbo].[GLL_OFFCGR] AS [ofg_f] WITH(NOLOCK) ON [ofc_f].[GROUP_CODE] = [ofg_f].[GROUP_CODE]
   WHERE 
		1=1
		AND [dpt_f].[GROUP_CODE] = (CASE WHEN @DeptOrGroup = ''G'' THEN @DeptValue ELSE ''ALL'' END)
		AND [ofc_f].[GROUP_CODE] = (CASE WHEN @OfficeOrGroup = ''G'' THEN @OfficeValue ELSE ''ALL'' END)
		AND [dpt].[ID] = (CASE WHEN @DeptOrGroup = ''D'' THEN @DeptValue ELSE [dpt].[ID] END)
		AND [ofc].[CODE] = (CASE WHEN @OfficeOrGroup = ''O'' THEN @OfficeValue ELSE [ofc].[CODE] END)
   '

EXEC [sp_executesql] 
	  @sql
	  , N'@OfficeOrGroup CHAR(1)
	  , @OfficeValue CHAR(5)
	  , @DeptOrGroup CHAR(1)
	  , @DeptValue CHAR(5)'
	  , @OfficeOrGroup
	  , @OfficeValue
	  , @DeptOrGroup
	  , @DeptValue;

END

GO