Help with Error Message

[DaylonRed]

Could someone help me out here? I have created a pretty large Stored
Procedure with about 5 different parameters however, when I run the
procedure I get the following error:

"Syntax error converting the varchar value 'select..."

Where do I even start to find this error?

Thanks in advance

[Simon Hayes]

"DaylonRed" <EC*******@profit-tech.com> wrote in message
news:83**************************@posting.google.c om...

Could someone help me out here? I have created a pretty large Stored
Procedure with about 5 different parameters however, when I run the
procedure I get the following error:

"Syntax error converting the varchar value 'select..."

Where do I even start to find this error?

Thanks in advance

You could debug the stored procedure from Query Analyzer to find the point
where the error occurs - right-click the proc and select debug.

The error message you give seem to be incomplete - it should tell you which
data type it is trying to convert to. As a complete guess, you're building a
dynamic SQL string somewhere in the procedure, and you've tried to
concatenate a varchar and an integer, but without seeing the code I don't
really know:

select 'x' + 1 -- error due to data type precedence
go
select 'x' + cast(1 as char(1)) -- works OK
go
Simon

[DaylonRed]

Thanks for responded Simon. You are right in the fact that I am trying
to concatenate some strings here but I really thought the datatypes were
consistant. Would you mind taking a look at the stored procedure? I
warn you, it is quite long. :(

alter procedure dbo.sp_copscm004 (
@Bank Integer=NULL,
@Region Integer=NULL,
@Branch Integer=NULL,
@Officer Integer=NULL,
@MinDate Datetime,
@MaxDate datetime=null
)
as

Declare
@Var_MinDate datetime,
@Var_MaxDate datetime,
@SQLMainStr varchar(8000),
@SQLWhereStr varchar(500)

Set @Var_MinDate = @Mindate
Set @Var_MaxDate = @Maxdate

If (@Bank is not Null)
Begin
Set @SQLMainStr=
'select
tbl_casemaster.acctno,
tbl_casemaster.PrimaryAcctHolder,
tbl_casemaster.ProductType,
tbl_casemaster.TaxID,
tbl_casemaster.opendate,
tbl_casemaster.closeddate,
tbl_casemaster.ChargeOff_Principle,
tbl_casemaster.ChargeOff_Fees,
tbl_casemaster.ChargeOff_Interest,
tbl_casemaster.ChargeOff_Misc,
tbl_casemaster.AcctOfficerPerm,
tbl_casemaster.AcctOfficerTemp,
tbl_casemaster.agencyused,
tbl_bank.bankname,
tbl_region.regionname,
tbl_branch.branchname
from tbl_casemaster
inner join tbl_bank
on tbl_casemaster.bankid = tbl_bank.bankid
inner join tbl_branch
on tbl_casemaster.branchid = tbl_branch.branchid
inner join tbl_region
on tbl_casemaster.regionid = tbl_region.regionid
where
(ChargeOff_Principle > 0 or
ChargeOff_Fees > 0 or
ChargeOff_Interest > 0 or
ChargeOff_Misc > 0)and
tbl_CaseMaster.bankid = ''' + @Bank + ''' and'
if (@Maxdate is not null)
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate >= ''' + Cast(@Var_MinDate as Varchar(100)) +
''' and
tbl_casemaster.opendate <= ''' + Cast(@Var_MaxDate as Varchar(100)) +
''''
end
else
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate = ''' + Cast(@Var_MinDate as Varchar(100)) +
''''
end
End
else if (@Region is not Null)
Begin
Set @SQLMainStr=
'select
tbl_casemaster.acctno,
tbl_casemaster.PrimaryAcctHolder,
tbl_casemaster.ProductType,
tbl_casemaster.TaxID,
tbl_casemaster.opendate,
tbl_casemaster.closeddate,
tbl_casemaster.ChargeOff_Principle,
tbl_casemaster.ChargeOff_Fees,
tbl_casemaster.ChargeOff_Interest,
tbl_casemaster.ChargeOff_Misc,
tbl_casemaster.AcctOfficerPerm,
tbl_casemaster.AcctOfficerTemp,
tbl_casemaster.agencyused,
tbl_bank.bankname,
tbl_region.regionname,
tbl_branch.branchname
from tbl_casemaster
inner join tbl_bank
on tbl_casemaster.bankid = tbl_bank.bankid
inner join tbl_branch
on tbl_casemaster.branchid = tbl_branch.branchid
inner join tbl_region
on tbl_casemaster.regionid = tbl_region.regionid
where
(ChargeOff_Principle > 0 or
ChargeOff_Fees > 0 or
ChargeOff_Interest > 0 or
ChargeOff_Misc > 0) and
tbl_CaseMaster.Regionid = ''' + @Region + ''' and'
if (@Maxdate is not null)
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate >= ''' + Cast(@Var_MinDate as Varchar(100)) +
''' and
tbl_casemaster.opendate <= ''' + Cast(@Var_MaxDate as Varchar(100)) +
''''
end
else
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate = ''' + Cast(@Var_MinDate as Varchar(100)) +
''''
end
End
else if (@Branch is not Null)
begin
Set @SQLMainStr=
'select
tbl_casemaster.acctno,
tbl_casemaster.PrimaryAcctHolder,
tbl_casemaster.ProductType,
tbl_casemaster.TaxID,
tbl_casemaster.opendate,
tbl_casemaster.closeddate,
tbl_casemaster.ChargeOff_Principle,
tbl_casemaster.ChargeOff_Fees,
tbl_casemaster.ChargeOff_Interest,
tbl_casemaster.ChargeOff_Misc,
tbl_casemaster.AcctOfficerPerm,
tbl_casemaster.AcctOfficerTemp,
tbl_casemaster.agencyused,
tbl_bank.bankname,
tbl_region.regionname,
tbl_branch.branchname
from tbl_casemaster
inner join tbl_bank
on tbl_casemaster.bankid = tbl_bank.bankid
inner join tbl_branch
on tbl_casemaster.branchid = tbl_branch.branchid
inner join tbl_region
on tbl_casemaster.regionid = tbl_region.regionid
where
(ChargeOff_Principle > 0 or
ChargeOff_Fees > 0 or
ChargeOff_Interest > 0 or
ChargeOff_Misc > 0)and
tbl_CaseMaster.Branchid = ''' + @Branch + ''' and'
if (@Maxdate is not null)
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate >= ''' + Cast(@Var_MinDate as Varchar(100)) +
''' and
tbl_casemaster.opendate <= ''' + Cast(@Var_MaxDate as Varchar(100)) +
''''
end
else
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate = ''' + Cast(@Var_MinDate as Varchar(100)) +
''''
end
End
else if (@Officer is not Null)
Begin
Set @SQLMainStr=
'select
tbl_casemaster.acctno,
tbl_casemaster.PrimaryAcctHolder,
tbl_casemaster.ProductType,
tbl_casemaster.TaxID,
tbl_casemaster.opendate,
tbl_casemaster.closeddate,
tbl_casemaster.ChargeOff_Principle,
tbl_casemaster.ChargeOff_Fees,
tbl_casemaster.ChargeOff_Interest,
tbl_casemaster.ChargeOff_Misc,
tbl_casemaster.AcctOfficerPerm,
tbl_casemaster.AcctOfficerTemp,
tbl_casemaster.agencyused,
tbl_bank.bankname,
tbl_region.regionname,
tbl_branch.branchname
from tbl_casemaster
inner join tbl_bank
on tbl_casemaster.bankid = tbl_bank.bankid
inner join tbl_branch
on tbl_casemaster.branchid = tbl_branch.branchid
inner join tbl_region
on tbl_casemaster.regionid = tbl_region.regionid
where
(ChargeOff_Principle > 0 or
ChargeOff_Fees > 0 or
ChargeOff_Interest > 0 or
ChargeOff_Misc > 0)and
tbl_casemaster.AcctOfficerPerm = ''' + @Officer + ''' and'
if (@Maxdate is not null)
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate >= ''' + Cast(@Var_MinDate as Varchar(100)) +
''' and
tbl_casemaster.opendate <= ''' + Cast(@Var_MaxDate as Varchar(100)) +
''''
end
else
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate = ''' + Cast(@Var_MinDate as Varchar(100)) +
''''
end
End
else
begin
Set @SQLMainStr=
'select
tbl_casemaster.acctno,
tbl_casemaster.PrimaryAcctHolder,
tbl_casemaster.ProductType,
tbl_casemaster.TaxID,
tbl_casemaster.opendate,
tbl_casemaster.closeddate,
tbl_casemaster.ChargeOff_Principle,
tbl_casemaster.ChargeOff_Fees,
tbl_casemaster.ChargeOff_Interest,
tbl_casemaster.ChargeOff_Misc,
tbl_casemaster.AcctOfficerPerm,
tbl_casemaster.AcctOfficerTemp,
tbl_casemaster.agencyused,
tbl_bank.bankname,
tbl_region.regionname,
tbl_branch.branchname
from tbl_casemaster
inner join tbl_bank
on tbl_casemaster.bankid = tbl_bank.bankid
inner join tbl_branch
on tbl_casemaster.branchid = tbl_branch.branchid
inner join tbl_region
on tbl_casemaster.regionid = tbl_region.regionid
where
(ChargeOff_Principle > 0 or
ChargeOff_Fees > 0 or
ChargeOff_Interest > 0 or
ChargeOff_Misc > 0)and'
if (@Maxdate is not null)
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate >= ''' + Cast(@Var_MinDate as Varchar(100)) +
''' and
tbl_casemaster.opendate <= ''' + Cast(@Var_MaxDate as Varchar(100)) +
''''
end
else
begin
Set @SQLWhereStr =
' tbl_casemaster.opendate = ''' + Cast(@Var_MinDate as Varchar(100)) +
''''
end
End

--exec(@SQLMainStr + @SQLWhereStr)

print(@SQLMainStr + @SQLWhereStr)
GO

DaylonRed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

DaylonRed (Da*******@developerex.com) writes:

Thanks for responded Simon. You are right in the fact that I am trying
to concatenate some strings here but I really thought the datatypes were
consistant. Would you mind taking a look at the stored procedure? I
warn you, it is quite long. :(

You have string + integer. string is lower in the type hierarchy than int,
so it is converted to integer, and then it goes downhill from there. You
appear to nevertless put @Bank in quotes in the dynamic SQL statement,
so maybe you have declare @Bank wrongly.

Better, though, is to use sp_executesql to run the dynamic SQL, in
which case you never have to run into this in the first place.

See http://www.sommarskog.se/dynamic_sql.html#sp_executesql for an
introduction.
See also http://www.sommarskog.se/dyn-search.html#sp_executesql for a
more elaborate example.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp