====== MS SQLServer Stored Procedure ======
===== Stored Procedure에서의 Dynamic Query =====
  * 동적 쿼리는 SQL Injection에 매우 유의해야한다. Stored Procedure에서 동적 쿼리를 생성할 때도 파라미터 방식으로 해야한다.
  * [[http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html|Sql server, .net and c# video tutorial: Dynamic SQL in Stored Procedure]]
  * [[https://www.youtube.com/watch?v=5m6dCbYO0Mk|(4) Prevent sql injection with dynamic sql - YouTube]]

<code sql>
-- 동적으로 생성하는 SQL 문자열에서 조건 값을 파라미터화 한다.
Create Procedure spSearchEmployeesGoodDynamicSQL
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary int = NULL
As
Begin
     Declare @sql nvarchar(max)
     Declare @sqlParams nvarchar(max)

     Set @sql = 'Select * from Employees where 1 = 1'
         
     if(@FirstName is not null)
          Set @sql = @sql + ' and FirstName=@FN'
     if(@LastName is not null)
          Set @sql = @sql + ' and LastName=@LN'
     if(@Gender is not null)
          Set @sql = @sql + ' and Gender=@Gen'
     if(@Salary is not null)
          Set @sql = @sql + ' and Salary=@Sal'

     Execute sp_executesql @sql,
     N'@FN nvarchar(50), @LN nvarchar(50), @Gen nvarchar(50), @sal int',
     @FN=@FirstName, @LN=@LastName, @Gen=@Gender, @Sal=@Salary
End
Go
</code>