Incorrect Syntax Near 'WHERE'

Transact-SQL
1

Greetings experts,

I pray that all is well and safe.

I have been stuck trying to resolve an error on the stored procedure below.

A little Background: When users attempt to look up cases, they can search by Casenumber, defendant Name or Plaintiff name.

These three are radio button options.

There is a search box where a user can enter a value.

If you user checks either the defendant name or plaintiff name, then user enters name value in the search box.

If user checks the casenumber box, the user must enter casenumber value in the search box in other to get results if record exists.
Finally, there is the case type. Case can be civil, criminal or both.

So, user checks a box for either defendant name, plaintiff name, or casenumber, enters search value in the search box and selects case type of civil, criminal,or both from dropdown.

The problem we have been having now for quite sometime is that regardless of whether user is search by civil, criminal or both case type, as long as user checks the plaintiff radio box, the page blows up with an error message: Incorrect syntax near 'WHERE'

Can someone please point me in the right direction of what I am doing wrong with the stored proc below?

ALTER PROCEDURE [dbo].[retrieveCases] @searchValue VARCHAR(max),
                                       @CaseType    VARCHAR(1),
                                       @DFName      BIT,
                                       @CaseNo      BIT,
                                       @PLName      BIT
AS
  BEGIN
      DECLARE @STR VARCHAR(max)

      -- BEGIN CIVIL CASE --
      IF @CaseType = '1'
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog1] WHERE '

            IF @CaseNo <> '0'
              BEGIN
                  SET @STR +=' CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'')  '
              END

            IF @DFName <> '0'
			BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				------------------------------------------------

				
				SET @STR +=' (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''DEFENDANT'')) '
			END             
           

            IF @PLName <> '0'
			BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0' or @DFName <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				-------------------------------------------------

				
				SET @STR +=' (CONTAINS([Name], ''"*'
								+ @searchValue + '*"'') AND CONTAINS([Type],''PLAINTIFF'' ))'
			END

       
       
        END
      -- END CIVIL CASE --
      -- BEGIN CRIMINAL CASE --
      ELSE IF @CaseType = '2'
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType   FROM [dbo].[SearchCatalog2] WHERE '

            IF @CaseNo <> '0'
              BEGIN
                 SET @STR +='CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

            IF @DFName <> '0'
              BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				------------------------------------------------

				SET @STR +='CONTAINS([NAME], ''"*'
                             + @searchValue + '*"'') '
                             
                
              END

         
        END
      -- END CRIMINAL CASE --
      -- BEGIN BOTH CASES --
      ELSE
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog1] WHERE '

            IF @CaseNo <> '0'
              BEGIN
                   SET @STR +='CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

            IF @DFName <> '0'
			BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				------------------------------------------------

				
				SET @STR +=' (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''DEFENDANT'')) '
			END             
           

            IF @PLName <> '0'
			BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0' or @DFName <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				-------------------------------------------------

				
				SET @STR +=' (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''PLAINTIFF'' ))'
			END

            SET @STR +=' UNION ALL '
            SET @STR +=' SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog2] WHERE '

            IF @CaseNo <> '0'
              BEGIN
                 SET @STR +='CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

			IF @DFName <> '0'
            BEGIN
				-- Set OR in case that previous condition exists
				IF @CaseNo <> '0'
				BEGIN
					SET @STR +=' OR '
				END
				------------------------------------------------

				SET @STR +='CONTAINS([NAME], ''"*'
                             + @searchValue + '*"'') '
                             
                
            END

        END

     print @Str
     EXEC (@STR)

  END
2

I think it's gonna be a pain and cause errors to constantly check all previous conditions. Maybe an approach that always adds ' OR ', then removes the first OR before the statement is run.

ALTER PROCEDURE [dbo].[retrieveCases] @searchValue VARCHAR(max),
@CaseType VARCHAR(1),
@DFName BIT,
@CaseNo BIT,
@PLName BIT
AS
BEGIN
DECLARE @STR VARCHAR(max)
DECLARE @STR_BYTE bigint

      -- BEGIN CIVIL CASE --
      IF @CaseType = '1'
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog1] WHERE'
            SET @STR_BYTE = LEN(@STR)

            IF @CaseNo <> '0'
              BEGIN
                  SET @STR +=' OR CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'')  '
              END

            IF @DFName <> '0'
			BEGIN
				SET @STR +=' OR (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''DEFENDANT'')) '
			END             
           

            IF @PLName <> '0'
			BEGIN  
				SET @STR +=' OR (CONTAINS([Name], ''"*'
								+ @searchValue + '*"'') AND CONTAINS([Type],''PLAINTIFF'' ))'
			END
            
            SET @STR = STUFF(@STR, @STR_BYTE, 3, ' ')       
       
        END
      -- END CIVIL CASE --
      -- BEGIN CRIMINAL CASE --
      ELSE IF @CaseType = '2'
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType   FROM [dbo].[SearchCatalog2] WHERE'
            SET @STR_BYTE = LEN(@STR)

            IF @CaseNo <> '0'
              BEGIN
                 SET @STR +=' OR CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

            IF @DFName <> '0'
              BEGIN
				SET @STR +=' OR CONTAINS([NAME], ''"*'
                             + @searchValue + '*"'') '                       
                
              END

            SET @STR = STUFF(@STR, @STR_BYTE, 3, ' ')

         
        END
      -- END CRIMINAL CASE --
      -- BEGIN BOTH CASES --
      ELSE
        BEGIN
            SET @STR='SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog1] WHERE'
            SET @STR_BYTE = LEN(@STR)

            IF @CaseNo <> '0'
              BEGIN
                   SET @STR +=' OR CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

            IF @DFName <> '0'
			BEGIN
				SET @STR +=' OR (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''DEFENDANT'')) '
			END             
           

            IF @PLName <> '0'
			BEGIN
				SET @STR +=' OR (CONTAINS([Name], ''"*'
							+ @searchValue + '*"'') AND CONTAINS([Type],''PLAINTIFF'' ))'
			END
            SET @STR = STUFF(@STR, @STR_BYTE, 3, ' ')


            SET @STR +=' UNION ALL '
            SET @STR +=' SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , [Status] , [Type], [DISPDATE] , CaseType  FROM [dbo].[SearchCatalog2] WHERE'
            SET @STR_BYTE = LEN(@STR) 

            IF @CaseNo <> '0'
              BEGIN
                 SET @STR +=' OR CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'') '
              END

			IF @DFName <> '0'
            BEGIN
				SET @STR +=' OR CONTAINS([NAME], ''"*'
                             + @searchValue + '*"'') '                         
                
            END

            SET @STR = STUFF(@STR, @STR_BYTE, 3, ' ')

        END

     print @Str
     EXEC (@STR)

  END
1 Like
3

Thanks a lot for the prompt response Scott. Much appreciated.

Took me this long to get back here because I was having issues with it and trying to see if I could figure out what's wrong.

For instance, when I tested with Defendant name, I got "Incorrect syntax near 'R'.

I could not figure out where the R is coming from.

When I tested the plaintiff that was the original error, the error was more weird.

I just wish there was a better to condense the code so I can have a better handle on it.

4

I think you can simplify this even more if you defaulted the parameters to NULL instead of 0 and maybe even remove dynamic SQL by sending them as null. Let me try that

Could you please provide Table definition and some sample data for SearchCatalog1?

Oh wait, this is not a table but an actual search catalog?

5

I think your issue might be

DECLARE @STR VARCHAR(max)

it defaults to NULL so when you do

 SET @STR +=' OR CONTAINS([CASENO], ''"*'
                             + @searchValue + '*"'')  '

it is still NULL.

The joys of dynamic query. I believe you need to set it as follows

DECLARE @STR VARCHAR(max) = ''


or change the first time that var is set to

SET @STR =' OR CONTAINS([CASENO], ''"'
+ @searchValue + '"'') '

6

Here is a no dynamic search approach that might work?

IF @CaseType = '1'
        BEGIN
            declare @SearchDFName nvarchar(250), 
@SearchCaseNo nvarchar(250),@SearchPLName nvarchar(250);

				select @SearchDFName = 
				   case when @DFName is null 
				   then null else '"*' + @searchValue + '*"' end;

			  select @SearchCaseNo = coaelesce(@CaseNo, '"*' + @searchValue + '*"');

			  select @SearchPLName = 
			  				   case when @PLName is null 
				   then null else '"*' + @searchValue + '*"' end;

			SELECT DISTINCT CaseId, [TypeId] , [CASENO] , [Name] , 
[Status] , [Type], [DISPDATE] , CaseType  
			 FROM [SearchCatalog1]
		where 1 = 1 and 
			( @SearchCaseNo is null OR CONTAINS([CASENO], @SearchCaseNo)   ) or
			( @SearchDFName is null OR ( CONTAINS([Name], @SearchDFName) AND CONTAINS([Type],'DEFENDANT')  ) ) or
 (@SearchPLName is null OR ( CONTAINS([Name], @SearchPLName) AND CONTAINS([Type],'PLAINTIFF')  ))
1 Like