Link to home
Start Free TrialLog in
Avatar of joein610
joein610

asked on

Query doesn't return what it supposed to

I have a stored procedure that I use for paging results. I wanted to add a search functionality so I simply added the 'orgname LIKE @SEARCH'. @SEARCH is a variable with enclosed % on both sides. The problem is that it doesn't run as it supposed to. Even if I search for a string that doesn't exist it ALWAYS returns all rows from the table.

This is the procedure:

GO
/****** Object:  StoredProcedure [dbo].[List_Migrations_Search]    Script Date: 11/14/2012 09:07:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[List_Migrations_Search]
	@PageSize INT,
	@Page INT,
	@SEARCH VARCHAR
AS
BEGIN
	DECLARE @TotalRows Int
	DECLARE @TotalPages INT
	IF (@SEARCH != '')
		BEGIN
			SELECT @TotalRows=COUNT(*) FROM tbl_BACKEND_tools_migrations_mcafee_schedule WHERE orgname LIKE @SEARCH
			PRINT 'SEARCHING'
		END
	ELSE
		BEGIN
			SELECT @TotalRows=COUNT(*) FROM tbl_BACKEND_tools_migrations_mcafee_schedule
		END
	PRINT @TotalRows
	SET @TotalPages = (@TotalRows /@PageSize) + 1
	IF(@Page<1)
		SET @Page=1
	IF(@Page>@TotalPages)
		SET @Page=@TotalPages
		With Records As
		(	
			SELECT
            @TotalPages AS TotalPages, ROW_NUMBER() OVER (ORDER BY ID DESC) AS Row, A.*,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains] WHERE SCH_ID = A.ID) AS NR_DOMAINS, 
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains_emailaccounts] WHERE SCH_ID = A.ID) AS NR_EMAILS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains_emailaccounts_aliases] WHERE SCH_ID = A.ID) AS NR_ALIASES,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains_aliases] WHERE SCH_ID = A.ID) AS DOMAIN_ALIASES,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_schedule_senders] WHERE SCH_ID = A.ID AND TYPE = 1) AS ORG_APPROVED_SENDERS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_schedule_senders] WHERE SCH_ID = A.ID AND TYPE = 0) AS ORG_BLOCKED_SENDERS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains_emailaccounts_senders] WHERE SCH_ID = A.ID AND TYPE = 1) AS USER_APPROVED_SENDERS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_domains_emailaccounts_senders] WHERE SCH_ID = A.ID AND TYPE = 0) AS USER_BLOCKED_SENDERS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_processing_errors] WHERE SCH_ID = A.ID) AS ERRORS,
            (SELECT COUNT(ID) FROM [tbl_BACKEND_tools_migrations_mcafee_adding_errors] WHERE SCH_ID = A.ID) AS MCAFEE_ERRORS
             FROM [tbl_BACKEND_tools_migrations_mcafee_schedule] A WHERE A.orgname LIKE @SEARCH
		)
	SELECT * FROM Records WHERE Row BETWEEN (@Page - 1) * @PageSize + 1 AND @Page * @PageSize AND orgname LIKE @SEARCH ORDER BY Row

END

Open in new window



This is how I run it:

EXEC List_Migrations_Search 15000, 1, '%dasdasdasdasdas%';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of tim_cs
tim_cs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of joein610
joein610

ASKER

It worked! Thank you very much. Why it didn't work without the perimeter?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Then it's VARCHAR(1) so that makes no sense for your LIKE.  Think if 50 characters are enough, should be length of column + 2 at least.