CODEWITHSUNDEEP
sql-serverstored-procedures
Teerth
Teerth

Reputation: 169

Stored procedure with dynamic query "Error"

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

Upvotes: 1

Views: 77

Answers (1)

Rick S
Rick S

Reputation: 6586

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.

Upvotes: 2

Related Questions