Expected Pending Operation

Question

I have 2 tables, each contains 4-500k entries

CREATE TABLE [dbo].[User][UserId] [int] IDENTITY(1,1) NOT NULL,
[Password] [nvarchar](max) NULL,
[RoleId] [int] NOT NULL,
[Name] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[Landline] [nvarchar](max) NULL,
[MobileNumberCode] [int] NULL,
[MobileNumber] [nvarchar](max) NULL,
[DateOfBirth] [datetime] NULL,
[MarriageDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[Status] [nvarchar](max) NOT NULL,
[BranchId] [int] NULL,
[UserTitle] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[HouseNumber] [nvarchar](50) NULL,
[BuildingNumber] [nvarchar](50) NULL,
[RoadNumber] [nvarchar](50) NULL,
[BlockNumber] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[NearBranchId] [int] NULL,
[MobileIsValid] [bit] NULL,
[EmailIsValid] [bit] NULL,
[Gender] [nvarchar](50) NULL,
[SourceId] [int] NULL)

CREATE TABLE [dbo].[PurchaseOrder]
[PurchaseOrderId] [int] NOT NULL,
[BranchId] [int] NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[Amount] [decimal](18, 3) NOT NULL,
[UserId] [int] NOT NULL,
[Status] [nvarchar](max) NULL,
[sbs_no] [int] NOT NULL)

And I stored the procedure to get data from these tables using join.

CREATE PROC Sp_SearchCustomer (@FromDate datetime = null,
@ToDate datetime = null,
@RegFromDate datetime = null,
@RegToDate datetime = null)
AS
BEGIN
  select a.UserId,a.Name,b.PurchaseOrderId,b.Amount from dbo.[User] a left      join PurchaseOrder b on a.UserId=b.UserId
where 
((a.CreatedDate >= ''' + cast(@RegFromDate as varchar) + ''')
AND (a.CreatedDate <= ''' + cast(@RegToDate as varchar) + ''')) 
and ((b.PurchaseDate >= ''' + cast(@FromDate as varchar) + ''')
AND (b.PurchaseDate <= ''' + cast(@ToDate as varchar) + ''')) 
END 

When performing this procedure with the date, its exception is "Waiting for operation". Please help solve this problem.

Answer 1

Your date in your tables and in your procedure is stored as varchar. This is perfect and there is no need to convert them to varchar.

In addition, varchar is surrounded by quotation marks and will not be executed. This is just a line:

 where ((a.CreatedDate >= 'cast(@RegFromDate as varchar)')...

There are also too many useless parentheses as you use AND.

Try this instead:

CREATE PROC Sp_SearchCustomer (
    @FromDate datetime = null,
    @ToDate datetime = null,
    @RegFromDate datetime = null,
    @RegToDate datetime = null
)
AS
BEGIN
    SELECT a.UserId
        ,a.Name
        ,b.PurchaseOrderId
        ,b.Amount 
    FROM dbo.[User] a 
    LEFT JOIN PurchaseOrder b 
        ON a.UserId = b.UserId
    WHERE 
        a.CreatedDate >= @RegFromDate 
        AND a.CreatedDate <= @RegToDate 
        AND b.PurchaseDate >= @FromDate 
        AND b.PurchaseDate <= @ToDate 
END 

, . , , .

: exec sp_updatestats % REBUILD REORGANIZE .