none
In SQL ,while passing the parameter in stored Procedure which dynamic construct where clause ,Values are not set correctly. RRS feed

  • General discussion

  • I have a SQL Running:

    exec sp_executesql N'PEMS_AI_GetParkingView @orderBy, @PageNumber, @PageSize,  @UserSessionKey,  @StartDate,  @EndDate,  @OffenceStartDate,
      @OffenceEndDate,  @CustomerId,  @UserId,  @Search,  @InfringementStatus,  @LocationStreet,  @LocationSuburb,  @OfficerID,  @OfficerName,  @UNITSERIAL, 
       @VehLicState,  @IssueNoStart,  @IssueNoEnd,  @LoginOfficerID,  @LoginOfficerName,  @SubModule',
       
       N'@orderBy nvarchar(18),@PageNumber int,@PageSize int,@UserSessionKey nvarchar(100),
       @StartDate nvarchar(4000),@EndDate nvarchar(4000),@OffenceStartDate nvarchar(4000),@OffenceEndDate nvarchar(4000),@CustomerId nvarchar(3),@UserId nvarchar(2),@Search nvarchar(4000),
       @InfringementStatus nvarchar(4000),@LocationStreet nvarchar(4000),@LocationSuburb nvarchar(4000),@OfficerID nvarchar(4000),@OfficerName nvarchar(4000),@UNITSERIAL nvarchar(4000),
       @VehLicState nvarchar(4000),@IssueNoStart nvarchar(4000),@IssueNoEnd nvarchar(4000),@LoginOfficerID nvarchar(2),@LoginOfficerName nvarchar(20),@SubModule nvarchar(6)',
       
       @orderBy=N'IssueDateTime desc',
       @PageNumber=1,@PageSize=100,@UserSessionKey=N'33@-1549812589',@StartDate=N'',@EndDate=N'',@OffenceStartDate=N'',@OffenceEndDate=N'',@CustomerId=N'666',@UserId=N'33',@Search=N'',@InfringementStatus=N'',
       @LocationStreet=N'',@LocationSuburb=N'',@OfficerID=N'',@OfficerName=N'',@UNITSERIAL=N'',@VehLicState=N'',@IssueNoStart=N'1014631',@IssueNoEnd=N'1019483',@LoginOfficerID=N'33',@LoginOfficerName=N'AXMyint',@SubModule=N'Parking'
    go

    When i see  In Sp what parameter is passed ,I can see parameter is not passed correctly.

    What can be the reasons for it?

    Monday, May 29, 2017 6:50 AM

All replies

  • The problem was that while calling the stored procedure , a parameter was not passed so, while generating the where clause dynamically it failed.
    Monday, May 29, 2017 7:07 AM