I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL Injection. Here is my stored procedure:
USE [TestDB] GO /****** Object: StoredProcedure [dbo].[Search_Dictionary] Script Date: 08/09/2018 19:17:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Search_Dictionary] @FilterBy int = NULL, @Name varchar(50) = NULL, @Code char(2) = NULL AS BEGIN SET NOCOUNT ON; SELECT RecID, Status, Code, Name FROM Dictionary WHERE (@FilterBy = 1 AND Name LIKE '%'+@Name+'%') OR (@FilterBy = 2 AND Code = @Code) OR (@FilterBy = 3 AND @Name IS NULL AND @Code IS NULL); END
Here is example on how I call this procedure:
EXEC Search_Dictionary @FilterBy = 1, @Name = "Grant", @Code = NULL;
I just want to prevent, if for example Filter By is 2 that should search query by Code column returns any result if user pass word Grant. In that case should return 0 records.
Also if anyone have any suggestions on how to improve the code please let me know.