Error near and - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error near and

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-02-25 : 11:48:46

But I am unable to locate it.

Thanks for tour help.

select @Qry = 'insert #TmpRptData
select
        RegID           = ''' + @RegID + ''',
        RptGrp          = 100,
        GrpName         = '''',
        ColName         = ''Cash In'',
        subColAmt       =null,
        subColCount     =null,
        ColAmt          = isnull(sum(LOAN_PICKUP.AMT),0),
        ColCount        = isnull(count(*),0),
		EmpID           =   '''+@EmpID+'''
 from  TRANS_HEADER inner join LOAN_PICKUP
		on TRANS_HEADER.TRAN_ID =    LOAN_PICKUP.TRAN_ID
		where '+ @MfilterBy2 +' 
		
		and TRANS_HEADER.VOID_CD=0
        and LOAN_PICKUP.VOID_CD=0
        and TRANS_HEADER.TRNING_MDE_FG=0
        and TRANS_HEADER.TRAN_CD = 21 ' + @filterBy+'
'
exec(@Qry)
PRINT @Qry

The print result is

insert #TmpRptData
select
        RegID           = 'ALL',
        RptGrp          = 100,
        GrpName         = '',
        ColName         = 'Cash In',
        subColAmt       =null,
        subColCount     =null,
        ColAmt          = isnull(sum(LOAN_PICKUP.AMT),0),
        ColCount        = isnull(count(*),0),
		EmpID           =   'ALL'
 from  TRANS_HEADER inner join LOAN_PICKUP
		on TRANS_HEADER.TRAN_ID =    LOAN_PICKUP.TRAN_ID
		where  BUS_DT = '02/25/2009 12:00:00 AM'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 11:57:26

This is where ur error is? Not sure what you want to do though

from TRANS_HEADER inner join LOAN_PICKUP
on TRANS_HEADER.TRAN_ID = LOAN_PICKUP.TRAN_ID
where '+ @MfilterBy2 +'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-25 : 12:34:37

Can you please post
1) Actual error
2) detail of #TmpRptData structure?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 13:01:53

You might want to check why you don't have any group by clause there

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-25 : 14:28:23

one of your @ must be NULL do an ISNULL or COALESCE(@Var, '')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-25 : 14:39:18

Could also be that the size of @qry is too small for the length of your statement.

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-02-25 : 14:53:17

Error message

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'and'.

table structure

create table #TmpRptData(
		
        RegID           varchar(10),
        RptGrp          int             null,
        GrpName         varchar(20)     null,
        ColName         varchar(50)     null,
        subColAmt       money           null,
        subColCount     int             null,
        ColAmt          money           null,
        ColCount        int             null,
		EmpID           varchar(50)
)

a piece of code

select @RegID=upper(@RegID)
select @MfilterBy1 =' and TILL_TND_CUR.PRD_AGGR_ID = ' + @BeginPeriod


if @PeriodType = 'Today' or @PeriodType = 'Yesterday'
        select @MfilterBy2 =' TRANS_HEADER.BUS_DT = ''' + substring(@BeginDate,1,10) + ' 12:00:00 AM'''
else
        select @MfilterBy2 =' TRANS_HEADER.BUS_DT between '''+ substring(@BeginDate,1,10) + ' 12:00:00 AM'' and ''' + substring(@EndDate,1,10)  + ' 12:00:00 AM'''

declare @Qry varchar(8000)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-25 : 16:04:20

post the final exec'd result of @qry. Can't tell where the error is by just the pieces. Your original print result of @qry doesn't even contain the word "and".

EDIT:
I meant post the final value of @qry. (not the exec'd results)

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-02-25 : 16:29:25

[code]insert #TmpRptData
select
RegID = 'ALL',
RptGrp = 100,
GrpName = '',
ColName = 'Cash In',
subColAmt =null,
subColCount =null,
ColAmt = isnull(sum(LOAN_PICKUP.AMT),0),
ColCount = isnull(count(*),0),
EmpID = 'ALL'
from TRANS_HEADER inner join LOAN_PICKUP
on TRANS_HEADER.TRAN_ID = LOAN_PICKUP.TRAN_ID
where TRANS_HEADER.BUS_DT = '02/25/2009 12:00:00 AM'

and TRANS_HEADER.VOID_CD=0
and LOAN_PICKUP.VOID_CD=0
and TRANS_HEADER.TRNING_MDE_FG=0
and TRANS_HEADER.TRAN_CD = 21[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-25 : 16:36:44

I don't see a reason for the syntax error. If you comment out the "exec(@Qry)" does the error go away? I'm thinking the error is somewhere else in the code.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources