Uite un exemplu bun, dat de Adam Machanic in "Expert SQL Server 2005 development":
CREATE
PROCEDURE GetEmployeeData
@EmployeeId INT = NULL,
@NationalIdNumber
NVARCHAR(15) = NULL
AS
BEGIN
SET
NOCOUNT ON
DECLARE
@sql NVARCHAR(MAX)
SET
@sql = '' +
'SELECT '
+
'ContactId, '
+
'LoginId, '
+
'Title '
+
'FROM HumanResources.Employee '
+
'WHERE 1=1 '
+
CASE
WHEN
@EmployeeId IS NULL THEN ''
ELSE
'AND EmployeeId = @EmployeeId '
END
+
CASE
WHEN
@NationalIdNumber IS NULL THEN ''
ELSE
'AND NationalIdNumber = @NationalIdNumber '
END
EXEC
sp_executesql
@sql
,
N
'@EmployeeId INT, @NationalIdNumber NVARCHAR(60)',
@EmployeeId
,
@NationalIdNumber
END
Atentie la "sp_executesql" - este parametrizat. Nu ai probleme nici cu "SQL injection" nici cu parametrii optionali.