Stored Procedure to prevent sql injection with optional parameters and server side Paging and Sorting

To prevent sql injection on dynamic sql queries
we must use parametrized sql queries.

You can generate stored procedure from tables using this tool
CodeGenerator from https://github.com/mijaved/CodeGenerator

The following example illustrates optional parametrized sql statement with user supplied sorting and paging parameters:

DECLARE
 @Mode    INT = 2,
 @UserId    INT = 0,
 @LoginId   VARCHAR(50)='',
 @FirstName   VARCHAR(50) ='',
 @LastName   VARCHAR(50)='',
 @Password   VARCHAR(32)='',
 @LastLoginDateFrom VARCHAR(32)='2012-09-19',
 @LastLoginDateTo VARCHAR(32)='2012-09-20',
 @SortBy    VARCHAR(32)='UserId',
 @SortOrder   VARCHAR(32)='DESC',
 @StartIndex   INT = 1,
 @MaxIndex   INT = 100;

IF @Mode = 1 --Without Paging
BEGIN
 SELECT UserId,FirstName,LastName,LoginId,LastLoginDate
 FROM SEC_TblUsers WHERE 1=1
 AND (UserId = @UserId OR @UserId IS NULL OR @UserId <= 0)
 AND (LoginId = @LoginId OR @LoginId IS NULL OR @LoginId = '')
 AND (FirstName LIKE '%' + @FirstName + '%' OR @FirstName IS NULL OR @FirstName = '')
 AND (LastName LIKE '%' + @LastName + '%' OR @LastName IS NULL OR @LastName = '')
 AND (Password = @Password OR @Password IS NULL OR @Password = '')
 AND (LastLoginDate >= @LastLoginDateFrom OR @LastLoginDateFrom IS NULL OR @LastLoginDateFrom = '')
 AND (LastLoginDate <= @LastLoginDateTo OR @LastLoginDateTo IS NULL OR @LastLoginDateTo = '')
 ORDER BY
 CASE WHEN @SortBy = 'UserId' AND @SortOrder = 'ASC' THEN UserId END ASC,
 CASE WHEN @SortBy = 'UserId' AND @SortOrder = 'DESC' THEN UserId END DESC;
END
IF @Mode = 2 --With Paging
BEGIN
 WITH T AS (SELECT ROW_NUMBER() OVER (ORDER BY --ID DESC 
  CASE WHEN @SortBy = 'UserId' AND @SortOrder = 'ASC' THEN UserId END ASC,
  CASE WHEN @SortBy = 'UserId' AND @SortOrder = 'DESC' THEN UserId END DESC
 ) AS ROWID, * FROM ( 
  SELECT UserId,FirstName,LastName,LoginId,LastLoginDate
  FROM SEC_TblUsers WHERE 1=1
  AND (UserId = @UserId OR @UserId IS NULL OR @UserId <= 0)
  AND (LoginId = @LoginId OR @LoginId IS NULL OR @LoginId = '')
  AND (FirstName LIKE '%' + @FirstName + '%' OR @FirstName IS NULL OR @FirstName = '')
  AND (LastName LIKE '%' + @LastName + '%' OR @LastName IS NULL OR @LastName = '')
  AND (Password = @Password OR @Password IS NULL OR @Password = '')
  AND (LastLoginDate >= @LastLoginDateFrom OR @LastLoginDateFrom IS NULL OR @LastLoginDateFrom = '')
  AND (LastLoginDate <= @LastLoginDateTo OR @LastLoginDateTo IS NULL OR @LastLoginDateTo = '')
 ) AS T) SELECT * FROM T 
 WHERE 0=0 --ROWID BETWEEN @StartIndex AND @MaxIndex
 AND (ROWID >= @StartIndex OR @StartIndex IS NULL OR @StartIndex <= 0)
 AND (ROWID <= @MaxIndex OR @MaxIndex IS NULL OR @MaxIndex <= 0)
END