SQLのパラメータとストアドプロシージャの効果的な使用方法

-- 基本的なSQLクエリ
SELECT * FROM employees WHERE department = 'Engineering';

-- パラメータを用いたSQLクエリ
DECLARE @departmentName NVARCHAR(50)
SET @departmentName = 'Engineering'
SELECT * FROM employees WHERE department = @departmentName;

-- ストアドプロシージャの作成
CREATE PROCEDURE GetEmployeesByDepartment
@departmentName NVARCHAR(50)
AS
SELECT * FROM employees WHERE department = @departmentName;
GO

-- ストアドプロシージャの呼び出し
EXEC GetEmployeesByDepartment @departmentName = 'Engineering';

-- ストアドプロシージャ内での条件分岐
CREATE PROCEDURE GetEmployees
@departmentName NVARCHAR(50),
@position NVARCHAR(50)
AS
IF @departmentName IS NOT NULL AND @position IS NOT NULL
BEGIN
  SELECT * FROM employees WHERE department = @departmentName AND position = @position;
END
ELSE IF @departmentName IS NOT NULL
BEGIN
  SELECT * FROM employees WHERE department = @departmentName;
END
ELSE
BEGIN
  SELECT * FROM employees;
END
GO