Insert works in SQL but not in SP 1

[Naith]

Hi,

When I execute an insert into a table in Analyzer, along the lines of this:

SELECT
cast(Sales_Org_ID as smallint),
sales_org,
pl2_classid,
pl2_class,
convert(decimal(18,1),right(fquarter,4) + '.' + left(fquarter,1)),
( 
	case when convert(int,round("fweek",1)) >= 10 
	then convert(decimal(18,2),right(fweek,4) + '.' + left(fweek,2))
	else convert(decimal(18,2),right(fweek,4) + '.0' + left(fweek,1)) 
	end
),
convert(int,replace("bill_qty",',',''))...

Everything's hunkydory, and the insert is successful.

If I take the same code, and stick the insert in a sproc, I get a syntax error when it tries to convert the bill_qty value to an integer, like I'm asking it to convert non-numeric characters. Well, we both know this isn't the case, as the SQL variant of the code ran through okay.

What's the stored procedure really complaining about?

 

[bborissov]

The right syntax for REPLACE is (if bill_qty is field):

convert(int,replace(bill_qty, ',', ''))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Naith]

That got it. I thought the double quotes were harmless in that context. Guess they're not.

Thanks

 

[Madawc]

COMMENT: I use SQL within Crystal. SQL would work with single quotes or double quotes. When added to Crystal as a Stored Procedure, only single quotes would work.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10