Getting error message
-
I'm getting an error message reading "Incorrect Syntax Near @strSql"
What I've done is I've placed my entire sql select statement into an nvarchar(I named it @strSql). Before I placed it into this nvarchar, everything worked fine. Now however, I get the error. The error is coming up at the bottom of my query where the line reads "Print @strSql". If I remove that line however, I simply get another error, telling me the previous line is incorrect, and so forth and so on.
Here's my entire code but as I said, it has something to do with my @strSql.
-------------------------------------------------------------------
begin
declare @BusSrcId int
declare @AdminId int
declare @FirmId int
declare @DaysOverdueRange int
Declare @minOverDue Varchar(4)
declare @strSql nvarchar(4000)
set @BusSrcId=2035
set @FirmId=2
set @DaysOverdueRange=2
If (@DaysOverdueRange Is Null)
Begin
set @minOverDue='0'
end
else If (@DaysOverdueRange=1)
Begin
Set @minOverDue='31'
End
Else if (@DaysOverdueRange=2)
Begin
Set @minOverDue='91'
End
Else if (@DaysOverdueRange=3)
Begin
Set @minOverDue='121'
End
Else if (@DaysOverdueRange=4)
Begin
Set @minOverDue='181'
End
Else if (@DaysOverdueRange=5)
Set @minOverDue='366'
set @strSql = '
select distinct billid as InvoiceNo, clientname as ClientName, clientholdstmtind as ClientOnHoldInd,
BillDate, (select Sum(IsNull(BBCConsFeeAmt,0))+Sum(IsNull(BBCServFeeAmt,0))
From TBillingBillcodes Where BBCBillId=billid) as InvoiceAmt,
(Select Top 1 IsNull((AdjBalAmt),0) From TAdjustments Where AdjBillID=billid Order By AdjId desc) as OverDueAmt,
(DateDiff(day,BillDate,GetDate())) as DaysOverDue,(busSrcLName + '+ char(39) + ', ' +char(39) + ' + busSrcFName) as BusSrcName,
(Select Top 1 IsNull(PerLName,'+ char(39)+''+ char(39)+') + ' + char(39) + ', ' + char(39) + '+ IsNull(PerFName,'+ char(39)+''+ char(39)+')
From TPersonnel
Where PerId=(Select PlanPerIdAdmin From TPlans Where PlanId=(Select Top 1 BBCPlanId From TBillingBillCodes
Where BBCBillId=billid))) as AdminName,
(select top 1 isNull(clientcontlname,'+ char(39)+ ''+ char(39)+')'+ char(39) + ', '
+ char(39) +' + IsNull(clientcontfname,'+ char(39)+''+ char(39)+')from tclients
where clientid=billclientid) as ClientContact, ClientContTitle, ClientContPhone from tbilling
inner join tclients on clientid=billclientid
inner join tplans on planclientid = tclients.clientid
inner join tclientcontacts on clientcontclientid=billclientid
inner join tbussources on bussrcid = clientcontbussrcid
where billpmtstatus != ' +char(39)+ 'PAID' + char(39) + ''
if (@bussrcid is not NUll)
begin
set @strSql = @strSql + ' and ClientContBusSrcId= ' + char(39) + @BusSrcId + char(39) + ''
end
if (@adminid is not NULL)
begin
set @strSql = @strSql + ' and PlanPerIdAdmin='+ char(39) +@AdminId+ char(39) +''
end
if (@firmid is not NULL)
begin
set @strSql = @strSql + ' and tplans.planbillfirmid = '+ char(39) + @Firmid + char(39) + ''
end
set @strSql = @strSql + ' And DateDiff(Day,BillDate,GetDate())>='+ char(39) +@minOverDue+ char(39) +''
if(@bussrcid is not NULL)
begin
set @strSql = @strSql + 'order by BusSrcName, ClientName, BillDate'
end
else
begin
set @strSql = @strSql + 'order by AdminName, ClientName, BillDate'
end
print @strSql
go
-------------------------------------------------------------
I know it probably looks like a bunch of jarbled sql code but does anyone know just by looking at the error message where I may be rinning into my problem?
Thanks in advance.
-Ryan
-
might be a problem with the int variables (@BusSrcId, @AdminId int, @FirmId int, @DaysOverdueRange) being used to generate the string...
try to CAST the int values as varchar and see if that helps...
if (@bussrcid is not NUll)
begin
set @strSql = @strSql + ' and ClientContBusSrcId= ' + char(39) + CAST(@BusSrcId as Varchar) + char(39) + ''
end
if (@adminid is not NULL)
begin
set @strSql = @strSql + ' and PlanPerIdAdmin='+ char(39) + CAST(@AdminId as Varchar)+ char(39) +''
end
if (@firmid is not NULL)
begin
set @strSql = @strSql + ' and tplans.planbillfirmid = '+ char(39) + CAST(@Firmid as Varchar) + char(39) + ''
end
-
All that "begin"s must "end"
The "end" statement is missing for the first "begin".
- Niranjan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply