asked on

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

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Kidd

ASKER

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

SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial