SQL Search Stored Procedure

Hello

I'd like to ask if I'm doing this Correctly?

I am building a VB.Net project that will send parameters to a SQL Stored Procedure (SQL Server 2012) when the User Searches then display the Results.

I have to search for the input string ANYWHERE in the Data so I have to have the % Before and after the Search Word in the Like.
I have to Return different Columns depending on Provided Parameters.
I have to SEARCH different Columns depending on Provided Parameters.
There are also different Filters based on the Provided parameters.

I have come up with the Following procedure.
My Questions are
Is this the Best/Correct way?
Is this Preventing SQL Injection?
I am Breaking the Search into words in vb.net and sending to the Procedure as Individual Parameters - is this the Best Way?

ALTER PROCEDURE [dbo].[X_SOLVE_SCH_STOCK_ITEMS] 
	-- Add the parameters for the stored procedure here
	@SearchText1 AS NVARCHAR(50) = '',
	@SearchText2 AS NVARCHAR(50) = '',
	@SearchText3 AS NVARCHAR(50) = '',
	@SearchText4 AS NVARCHAR(50) = '',
	@SearchText5 AS NVARCHAR(50) = '',
	@SearchText6 AS NVARCHAR(50) = '',
	@SearchText7 AS NVARCHAR(50) = '',
	@SearchText8 AS NVARCHAR(50) = '',
	@SearchText9 AS NVARCHAR(50) = '',
	@SearchText10 AS NVARCHAR(50) = '',
	@InActive BIT = 0, 
	@Common BIT = 0,
	@ExtendedSearch BIT = 0,
	@ReturnNameOnly BIT = 0,
	@ReturnAllFields BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @SQLSelectFields as NVarchar(500) = ''
	DECLARE @SQLString as NVarchar(MAX) = ''
	DECLARE @SQLFilter as NVarchar(200) = ''
	DECLARE @SQLParam AS NVarchar(1000)

    -- Insert statements for procedure here

	--Check which Fields are Required and add to Fields Variable
	if @ReturnNameOnly = 1
		begin
			set @SQLSelectFields = 'SI.STOCKCODE,SI.DESCRIPTION'
		end
	else if @ReturnAllFields = 1
		begin
			set @SQLSelectFields = 'SI.STOCKCODE,SI.DESCRIPTION,SELLPRICE1,SELLPRICE2'
		end;

	-- Check what Filters are to Be Applied and Add to the Filter Variable
	if @InActive = 0
		begin
			set @SQLFilter = ' and SI.ISACTIVE = ''Y'''
		end
	if @Common = 1
		begin
			set @SQLFilter = @SQLFilter + ' and SI.X_COMMON = Y'
		end

	-- Trim the First Search word
	set @SearchText1 = LTRIM(RTRIM(ISNULL(@SearchText1,'')))

	-- If only one Search word is supplied then search for the Stockcode (So if a Stockcode is Entered then only that item is returned)
	if LEN(LTRIM(RTRIM(ISNULL(@SearchText2,'')))) = 0 and LEN(LTRIM(RTRIM(@SearchText1))) > 0
		begin
			set @SQLString = 'Select ' + @SQLSelectFields + ' from STOCK_ITEMS SI Where SI.STOCKCODE = @SearchText1'
			execute sp_executesql @SQLString, N'@SearchText1 NVARCHAR(50)', @SearchText1
			return;
		end
	-- If Search Text is Supplied then Search for the Stock Item
	else if LEN(RTRIM(@SearchText1)) > 0
		begin
			-- Trim and Add the % to the Search words
			set @SearchText1 = '%' + LTRIM(RTRIM(ISNULL(@SearchText1,''))) + '%'
			set @SearchText2 = '%' + LTRIM(RTRIM(ISNULL(@SearchText2,''))) + '%'
			set @SearchText3 = '%' + LTRIM(RTRIM(ISNULL(@SearchText3,''))) + '%'
			set @SearchText4 = '%' + LTRIM(RTRIM(ISNULL(@SearchText4,''))) + '%'
			set @SearchText5 = '%' + LTRIM(RTRIM(ISNULL(@SearchText5,''))) + '%'
			set @SearchText6 = '%' + LTRIM(RTRIM(ISNULL(@SearchText6,''))) + '%'
			set @SearchText7 = '%' + LTRIM(RTRIM(ISNULL(@SearchText7,''))) + '%'
			set @SearchText8 = '%' + LTRIM(RTRIM(ISNULL(@SearchText8,''))) + '%'
			set @SearchText9 = '%' + LTRIM(RTRIM(ISNULL(@SearchText9,''))) + '%'
			set @SearchText10 = '%' + LTRIM(RTRIM(ISNULL(@SearchText10,''))) + '%'

			-- If Extended Search is True then Seach the Extended Fields
			if @ExtendedSearch = 1
				begin

					set @SQLString = 'Select Distinct ' + @SQLSelectFields + ' from STOCK_ITEMS SI INNER JOIN SUPPLIER_STOCK_ITEMS SSI ON SSI.STOCKCODE = SI.STOCKCODE Where ' + 
							'(SI.DESCRIPTION Like @SearchText1 OR SI.STOCKCODE LIKE @SearchText1 OR SI.X_OLD_STOCKCODE LIKE @SearchText1 OR SSI.SUPPLIERCODE LIKE @SearchText1) AND ' + 
							'(SI.DESCRIPTION Like @SearchText2 OR SI.STOCKCODE LIKE @SearchText2 OR SI.X_OLD_STOCKCODE LIKE @SearchText2 OR SSI.SUPPLIERCODE LIKE @SearchText2) AND ' + 
							'(SI.DESCRIPTION Like @SearchText3 OR SI.STOCKCODE LIKE @SearchText3 OR SI.X_OLD_STOCKCODE LIKE @SearchText3 OR SSI.SUPPLIERCODE LIKE @SearchText3) AND ' + 
							'(SI.DESCRIPTION Like @SearchText4 OR SI.STOCKCODE LIKE @SearchText4 OR SI.X_OLD_STOCKCODE LIKE @SearchText4 OR SSI.SUPPLIERCODE LIKE @SearchText4) AND ' + 
							'(SI.DESCRIPTION Like @SearchText5 OR SI.STOCKCODE LIKE @SearchText5 OR SI.X_OLD_STOCKCODE LIKE @SearchText5 OR SSI.SUPPLIERCODE LIKE @SearchText5) AND ' + 
							'(SI.DESCRIPTION Like @SearchText6 OR SI.STOCKCODE LIKE @SearchText6 OR SI.X_OLD_STOCKCODE LIKE @SearchText6 OR SSI.SUPPLIERCODE LIKE @SearchText6) AND ' + 
							'(SI.DESCRIPTION Like @SearchText7 OR SI.STOCKCODE LIKE @SearchText7 OR SI.X_OLD_STOCKCODE LIKE @SearchText7 OR SSI.SUPPLIERCODE LIKE @SearchText7) AND ' + 
							'(SI.DESCRIPTION Like @SearchText8 OR SI.STOCKCODE LIKE @SearchText8 OR SI.X_OLD_STOCKCODE LIKE @SearchText8 OR SSI.SUPPLIERCODE LIKE @SearchText8) AND ' + 
							'(SI.DESCRIPTION Like @SearchText9 OR SI.STOCKCODE LIKE @SearchText9 OR SI.X_OLD_STOCKCODE LIKE @SearchText9 OR SSI.SUPPLIERCODE LIKE @SearchText9) AND ' + 
							'(SI.DESCRIPTION Like @SearchText10 OR SI.STOCKCODE LIKE @SearchText10 OR SI.X_OLD_STOCKCODE LIKE @SearchText10 OR SSI.SUPPLIERCODE LIKE @SearchText10)' +
							@SQLFilter

					execute sp_executesql @SQLString, N'@SearchText1 NVARCHAR(50), @SearchText2 NVARCHAR(50), @SearchText3 NVARCHAR(50), @SearchText4 NVARCHAR(50), @SearchText5 NVARCHAR(50), @SearchText6 NVARCHAR(50), @SearchText7 NVARCHAR(50), @SearchText8 NVARCHAR(50), @SearchText9 NVARCHAR(50), @SearchText10 NVARCHAR(50)'
													, @SearchText1, @SearchText2, @SearchText3, @SearchText4, @SearchText5, @SearchText6, @SearchText7, @SearchText8, @SearchText9, @SearchText10
					return;
				end
			else -- Extended Search False - Just search Standard Fields
				begin
					set @SQLString = 'Select ' + @SQLSelectFields + ' from STOCK_ITEMS SI Where ' + 
							'(SI.DESCRIPTION Like @SearchText1 OR SI.STOCKCODE LIKE @SearchText1 OR SI.X_OLD_STOCKCODE LIKE @SearchText1) AND ' + 
							'(SI.DESCRIPTION Like @SearchText2 OR SI.STOCKCODE LIKE @SearchText2 OR SI.X_OLD_STOCKCODE LIKE @SearchText2) AND ' + 
							'(SI.DESCRIPTION Like @SearchText3 OR SI.STOCKCODE LIKE @SearchText3 OR SI.X_OLD_STOCKCODE LIKE @SearchText3) AND ' + 
							'(SI.DESCRIPTION Like @SearchText4 OR SI.STOCKCODE LIKE @SearchText4 OR SI.X_OLD_STOCKCODE LIKE @SearchText4) AND ' + 
							'(SI.DESCRIPTION Like @SearchText5 OR SI.STOCKCODE LIKE @SearchText5 OR SI.X_OLD_STOCKCODE LIKE @SearchText5) AND ' + 
							'(SI.DESCRIPTION Like @SearchText6 OR SI.STOCKCODE LIKE @SearchText6 OR SI.X_OLD_STOCKCODE LIKE @SearchText6) AND ' + 
							'(SI.DESCRIPTION Like @SearchText7 OR SI.STOCKCODE LIKE @SearchText7 OR SI.X_OLD_STOCKCODE LIKE @SearchText7) AND ' + 
							'(SI.DESCRIPTION Like @SearchText8 OR SI.STOCKCODE LIKE @SearchText8 OR SI.X_OLD_STOCKCODE LIKE @SearchText8) AND ' + 
							'(SI.DESCRIPTION Like @SearchText9 OR SI.STOCKCODE LIKE @SearchText9 OR SI.X_OLD_STOCKCODE LIKE @SearchText9) AND ' + 
							'(SI.DESCRIPTION Like @SearchText10 OR SI.STOCKCODE LIKE @SearchText10 OR SI.X_OLD_STOCKCODE LIKE @SearchText10)' +
							@SQLFilter

					execute sp_executesql @SQLString, N'@SearchText1 NVARCHAR(50), @SearchText2 NVARCHAR(50), @SearchText3 NVARCHAR(50), @SearchText4 NVARCHAR(50), @SearchText5 NVARCHAR(50), @SearchText6 NVARCHAR(50), @SearchText7 NVARCHAR(50), @SearchText8 NVARCHAR(50), @SearchText9 NVARCHAR(50), @SearchText10 NVARCHAR(50)'
													, @SearchText1, @SearchText2, @SearchText3, @SearchText4, @SearchText5, @SearchText6, @SearchText7, @SearchText8, @SearchText9, @SearchText10
					return;
				end
		end
	else
		begin
			return
		end


END

Open in new window



I'd like to get it Correct before I code the other 20+ Searches

Many Thanks
p-platerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Honestly I would look at implementing full-text indexing on the searchable fields and using that functionality instead.  I don't think you will find that this is a scalable solution and it is going to take longer and longer to execute as your STOCK_ITEMS table expands.

I don't see a problem with sql injection as you are using the parameter within sp_executesql.

I would send the search terms as a single delimited string and split it out within the procedure.  This will remove the 10 term limit and simplify the parameter profile.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p-platerAuthor Commented:
Thanks Brian.

The Reason I haven't gone to Full Test Search is I if I search for "Drill" it won't return "Coredrill"

I have experimented with copying the Data from the Search columns into a New column and splitting the words (So "Coredrill" becomes "coredrill oredrill redrill edrill drill rill ill ll l") then setting FTS index on this column. That works (And is faster) but it's to much work for this Project.

Am I correct with the way I see FTS to work? - Or is there another way to achieve "Coredrill" to be returned when the user searches for "drill"


To Split the String in the Procedure wouldn't I would need to run a Cursor to loop through the search terms to build the SQL Like String? - I would have thought it would be slower that way?

5 would be an acceptable Limit for the number or search words in this Project - So I have doubled it to be sure.

BTW - It takes just under half a second with 110,000 Products
Brian CroweDatabase AdministratorCommented:
It will handle stemming and pluralization like "run" returning "ran" and "running" but not the partials that you are talking about.  If you want that kind of functionality then you are pretty much stuck with LIKE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.