-4
CREATE Procedure Test12345
    @Page INT = 0,                                                                                                      
    @RecsPerPage BIGINT = 5,
    @IsExport BIT = 0,
    @SearchResult BIT = 0,
    @ClaimName VARCHAR(100),
    @ClaimNumber VARCHAR(100),
    @ServiceProviderName VARCHAR(100),
    @ZipCode VARCHAR(10),
    @City VARCHAR(100),
    @State INT,
    @DateReceivedFrom DATETIME,
    @DateReceivedTo DATETIME,
    @PolicyNumber VARCHAR(100),
    @InsuranceComapany INT,
    @ClaimType INT,
    @FileStatus INT, 
    @LoggedInUserId BIGINT
AS
    DECLARE @SqlQuery NVARCHAR(MAX)
BEGIN


    SET @SqlQuery = 'SELECT clm.ClaimId,
                                clm.ServiceTypeId,
                                cmpny.CompanyName,
                                clm.ClaimNumber,
                                (select top 1 ReferrerFirstName + '' '' + ReferrerLastName from ClaimReferrers cr1 where cr1.ClaimId = clm.ClaimId) as [ReferrerName],
                                (SELECT ReferrerType from ReferrerTypes WHERE ReferrerTypeId = clm.ReferrerTypeId) AS [ReferrerType], --clm.ReferrerTypeId),
                                (SELECT CompanyName FROM Companies WHERE CompanyId = (SELECT HeadCompanyId FROM Users WHERE UserId = aa.OAUserId)) AS [SPCompanyName],
                                vcd.Year,
                                vcd.Make,
                                vcd.AutoModel,
                                clm.DateOfLoss,
                                aa.AutoAssignmentDate,
                                aa.FileStatus,
                                (SELECT StatusDescription FROM AssignmentStatus WHERE StatusId = aa.FileStatus) AS StatusDescription,
                                aa.AutoAssignmentId
                        FROM    Companies cmpny INNER JOIN Claims clm ON cmpny.CompanyId = clm.HeadCompanyId 
                                                INNER JOIN AutoAssignments aa ON clm.ClaimId = aa.ClaimId 
                                                INNER JOIN VehicleClaimDetails vcd ON aa.AutoAssignmentId = vcd.AssignmentID
                                                INNER JOIN Users SPUser ON aa.OAUserId = SPUser.UserId
                        WHERE
                                clm.IsNugeAssignment = 1';


    IF @ClaimName IS NOT NULL
        SET @SqlQuery =  @SqlQuery + ' AND (clm.InsuredFirstName like ''%@ClaimName%'' OR clm.InsuredLastName like ''%@ClaimName%'')';

    IF @ClaimNumber IS NOT NULL
        SET @SqlQuery =  @SqlQuery + ' AND clm.ClaimNumber LIKE ''%@ClaimNumber%'''

    IF @ServiceProviderName IS NOT NULL
         SET @SqlQuery = @SqlQuery + ' AND exists(select * from companies where CompanyId=SPUser.HeadCompanyid and CompanyName LIKE ''%@ServiceProviderName%'')'   ; 

    IF @ZipCode IS NOT NULL
        SET @SqlQuery = @SqlQuery + ' AND (clm.OwnerZip =  @ZipCode OR clm.PropertyZip = @ZipCode)' ;

    IF @City IS NOT NULL
        SET @SqlQuery = @SqlQuery + ' AND (clm.OwnerCity LIKE ''%@City%'' OR clm.PropertyCity Like ''%@City%'')' ;

    IF @State IS NOT NULL
        SET @SqlQuery =  @SqlQuery + ' AND (clm.InsuredState = @State OR clm.PropertyState = @State)'  ;

    IF @DateReceivedFrom IS NOT NULL OR @DateReceivedTo IS NOT NULL  
    BEGIN   
         IF @DateReceivedFrom IS NOT NULL OR @DateReceivedTo IS NOT NULL                                                            
         BEGIN

             SET @DateReceivedFrom = ISNULL(@DateReceivedFrom,'01/01/1900')                                                               
             SET @DateReceivedTo = ISNULL(@DateReceivedTo,dbo.fn_GetLocalDateTime())     

             SET @SqlQuery = @SqlQuery + ' AND CAST(aa.dateRecieved AS DATE) BETWEEN CAST(@DateReceivedFrom AS DATE) AND CAST(@DateReceivedTo AS DATE)'    ;                                                              
        END 
    END

    IF @PolicyNumber IS NOT NULL
         SET @SqlQuery = @SqlQuery + ' AND clm.PolicyNumber LIKE ''%@PolicyNumber%''';

    IF @InsuranceComapany IS NOT NULL AND @InsuranceComapany <> '0'
        SET @SqlQuery = @SqlQuery + ' AND cmpny.HeadCompanyId = @HeadCompanyId';

    IF @ClaimType IS NOT NULL
        SET  @SqlQuery = @SqlQuery + ' AND clm.ClaimTypeId = @ClaimType';

    IF @FileStatus IS NOT NULL AND EXISTS (SELECT * FROM AssignmentStatus WHERE StatusId = @FileStatus)
        SET  @SqlQuery = @SqlQuery + ' AND aa.FileStatus = @FileStatus';

    SET  @SqlQuery = @SqlQuery + ' ORDER By clm.ClaimId OFFSET (@Page * @RecsPerPage) ROWS FETCH NEXT @RecsPerPage ROWS ONLY'


    DECLARE @SQLPARAMETERS NVARCHAR(2000)                                                                                                                                    
    SET @SQLPARAMETERS =+ ' @Page INT,'
    SET @SQLPARAMETERS =+ ' @RecsPerPage BIGINT,'
    SET @SQLPARAMETERS =+ ' @IsExport BIT,'
    SET @SQLPARAMETERS =+ ' @SearchResult BIT,'
    SET @SQLPARAMETERS =+ ' @ClaimName VARCHAR(100),'
    SET @SQLPARAMETERS =+ ' @ClaimNumber VARCHAR(100),'
    SET @SQLPARAMETERS =+ ' @ServiceProviderName VARCHAR(100),'
    SET @SQLPARAMETERS =+ ' @ZipCode VARCHAR(10),'
    SET @SQLPARAMETERS =+ ' @City VARCHAR(100),'
    SET @SQLPARAMETERS =+ ' @State INT,'
    SET @SQLPARAMETERS =+ ' @DateReceivedFrom DATETIME,'
    SET @SQLPARAMETERS =+ ' @DateReceivedTo DATETIME,'
    SET @SQLPARAMETERS =+ ' @PolicyNumber VARCHAR(100),'
    SET @SQLPARAMETERS =+ ' @InsuranceComapany INT,'
    SET @SQLPARAMETERS =+ ' @ClaimType INT,'
    SET @SQLPARAMETERS =+ ' @FileStatus INT,' 
    SET @SQLPARAMETERS =+ ' @LoggedInUserId BIGINT'


    EXEC SP_EXECUTESQL  
          @SqlQuery, 
          @SQLPARAMETERS, 
          @Page,
          @RecsPerPage,
          @IsExport,
          @SearchResult,
          @ClaimName,
          @ClaimNumber,
          @ServiceProviderName,
          @ZipCode,
          @City,
          @State,
          @DateReceivedFrom,
          @DateReceivedTo,
          @PolicyNumber,
          @InsuranceComapany,
          @ClaimType,
          @FileStatus,
          @LoggedInUserId
END

When control goes inside if block then only getting this error for that parameter only. This error gets thrown for all parameters which are passed to stored procedure.

**** EDITED ****

  • Declare parameters to pass to dynamic query
  • Passed all variables, still getting same error as:

enter image description here

EDIT-II enter image description here

closed as off-topic by Erik Darling, mustaccio, Michael Green, McNets, Joe Obbish May 16 '17 at 1:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, mustaccio, Michael Green, McNets, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.

3

There are quite a few things here that are incorrect:

  1. You state that there is an error but have not pasted the exact error into the question. Which scalar variable is SQL Server complaining about? That info helps.

  2. Your use of Dynamic SQL is incorrect:

    1. SET @SQLPARAMETERS = ' @Page INT,' does not concatenate each line as you are expecting it to. It resets the variable to be just that specific value each time.

    2. Once you define the parameter list correctly you need to then pass in those variables into sp_executesql.

    3. Once you pass those variables in, some of the LIKE clauses will not work as you are expecting. LIKE needs to have the % literals single-quoted and concatenated with the variable, such as: LIKE '%' + @something + '%'

    4. The abbreviated / shortened notation for string concatenation is +=, not =+. The =+ is not valid syntax but for some reason does not error. Instead it simply acts as =.

    5. When building @SQLPARAMETERS, either initialize in the declare as = N'', or the first SET @SQLPARAMETERS needs to be =, not +=. This works just like how you are building @SqlQuery.

  3. Minor, stylistic note: this is SQL Server, not PostgreSQL, so the DECLARE statements (e.g. DECLARE @SqlQuery) go after the BEGIN. Hence it should be AS BEGIN.

  4. When working with Dynamic SQL, it is always helpful to see the query being submitted to EXEC / sp_executesql, so for debugging purposes, add the following just above the EXEC sp_executesql line:

    PRINT '--------';
    PRINT @SqlQuery;
    PRINT '--------';
    PRINT @SqlParameters;
    PRINT '--------';
    

    Doing that will show that you have not actually done the concatenation since =+ is not valid syntax for that (and I don't know why it didn't throw a parse error, but it doesn't). This is item 2.4 above.

Not the answer you're looking for? Browse other questions tagged or ask your own question.