Stored procedure with dynamic query "Error"

Question

what wrong with my code getting the error while executing the application

Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'and'.

And Here is my Code. any help will be appreciated.

ALTER PROCEDURE [dbo].[Usp_ReportList]
            @pAccountType varchar(35)=null,
    @pFromDate datetime=null,
    @pToDate datetime=null,
    @pAccountId int=null,
    @pUserId int=null,
    @pTeamId int=null

AS
BEGIN
SET NOCOUNT ON;
Declare @strSQL AS NVarchar(4000)
SET @strSQL ='
SELECT  
        OrderInfoId,
        BorrowerFirstName,
        BorrowerLastName, 
        RequestedURL,
        Requests,
        CustomerUserID As LenderId ,
        o.RequestIPAddress As OriginatingIPAddress, 
        o.Requests As Status
from orderInfo o '

if(@pAccountType = 'Lender')
    BEGIN
    SET @strSQL += 'inner join [User] u on o.CustomerUserId = u.UID where 1=1'
END
 else if(@pAccountType = 'Affiliate')
    BEGIN
    SET @strSQL += 'inner join [User] u on o.AffiliateID = u.UID where 1=1'
END

if(@pFromDate != '')
    BEGIN
    SET @strSQL += ' and o.RequestDateTime >= ''' + CONVERT(VARCHAR(25),@pFromDate) +  ''''
END

if(@pToDate != '')
    BEGIN
    SET @strSQL += ' and o.RequestDateTime <= ''' + CONVERT(VARCHAR(25),@pToDate) + ''''
END

if(@pAccountId != '')
    BEGIN
    SET @strSQL += ' and u.UID in ( select UID from [User] where AccountID = ' + CONVERT(VARCHAR(10),@pAccountId) + ') '
END

if(@pUserID !='')
    BEGIN
    SET @strSQL += ' and u.UserId = ' + CONVERT(VARCHAR(10),@pUserId) + ' '
END

if(@pTeamId !='')
    BEGIN
    SET @strSQL += ' and u.TeamId = ' + CONVERT(VARCHAR(10),@pTeamId) + ' '
END


SET @strSQL += '
GROUP BY 
    OrderInfoId,
    BorrowerFirstName,
    BorrowerLastName, 
    RequestedURL,
    Requests,
    CustomerUserID,
    o.RequestIPAddress, 
    o.Requests'

EXEC (@strSQL) 
--PRINT (@strSQL)
END




Exec Usp_ReportList 'Lender','2015-06-01 00:00:00','2015-06-02 00:00:00','2','1'

here's my profilers genereated query

exec sp_executesql N'Exec Usp_ReportList @pAccountType,@pFromDate,@pToDate,@pAccountId,@pUserId,@pTeamId',N'@pAccountType nvarchar(1),@pFromDate datetime,@pToDate datetime,@pAccountId int,@pUserId int,@pTeamId int',@pAccountType=N'1',@pFromDate='2015-06-01 00:00:00',@pToDate='2015-06-02 00:00:00',@pAccountId=1,@pUserId=2,@pTeamId=2

getting error

Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'and'.

can not able to figure it out where the actual problem

The generated statement using PRINT (@strSQL)

SELECT  
        OrderInfoId,
        BorrowerFirstName,
        BorrowerLastName, 
        RequestedURL,
        Requests,
        CustomerUserID As LenderId ,
        o.RequestIPAddress As OriginatingIPAddress, 
        o.Requests As Status
from orderInfo o  and o.RequestDateTime >= 'Jun  1 2015 12:00AM' and o.RequestDateTime <= 'Jun  1 2015 12:00AM' and u.UID in ( select UID from [User] where AccountID = 2)  and u.UserId = 5  and u.TeamId = 5 
GROUP BY 
    OrderInfoId,
    BorrowerFirstName,
    BorrowerLastName, 
    RequestedURL,
    Requests,
    CustomerUserID,
    o.RequestIPAddress, 
    o.Requests

Rick S · Accepted Answer · 2015-06-03 15:53:41Z

2

I think you want to append your parameters to your query, like below. Also for dates and strings you need to put them inside single quotes like this '2014-01-01'

IF(@pFromDate != '')
BEGIN
    SET @strSQL += ' and o.RequestDateTime >= ''' +  @pFromDate + ''''
END

Note: I'm not positive I have the quoting correct to make sure your date is inside single quotes. Print out your SQL statement to make sure it looks correct before executing.

edited Jun 3, 2015 at 15:53

answered Jun 2, 2015 at 17:40

Rick S

6,5966 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Teerth Over a year ago

having hard time with this one, now quotes are fine , but when executing it in application i'm getting following error Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'and'., can you please help what's wrong here

Rick S Over a year ago

Put your PRINT statement before the EXEC and post the SQL statement so I can review it.

Teerth Over a year ago

missing this join statement inner join [User] u on o.CustomerUserId = u.UID there

Collectives™ on Stack Overflow

Stored procedure with dynamic query "Error"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related