Problem with simple Where Clause

[Tony]

Please Help me. I have a Stored Proc as follows:

USE feesched
GO

CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
AS

-- copy records with alpha in pos 1 that's not J
SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
INTO t_AveragedMedicare
FROM AveragedMedicare
WHERE NOT ISNUMERIC(LEFT([cpt code],1))
AND LEFT([cpt code],1) <> 'J'

I'm getting the following error message:
Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare, Line 10
Incorrect syntax near the keyword 'AND'.

Can anyone help me out?
Thanks!
Tony

[Simon Hayes]

"Tony" <to******@mchsi.com> wrote in message
news:6b**************************@posting.google.c om...

Please Help me. I have a Stored Proc as follows:

USE feesched
GO

CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
AS

-- copy records with alpha in pos 1 that's not J
SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
INTO t_AveragedMedicare
FROM AveragedMedicare
WHERE NOT ISNUMERIC(LEFT([cpt code],1))
AND LEFT([cpt code],1) <> 'J'

I'm getting the following error message:
Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare, Line 10 Incorrect syntax near the keyword 'AND'.

Can anyone help me out?
Thanks!
Tony

ISNUMERIC() returns an integer, so I guess you want this:

WHERE ISNUMERIC(LEFT([cpt code],1)) = 0

However, be aware that ISNUMERIC() returns 1 for many things which you may
not consider to be a number, as it will evaluate conversion to any numeric
data type, not just integers. Perhaps this would be better:

WHERE [cpt code] NOT LIKE '[0-9]%'
AND [cpt code] NOT LIKE 'J%'

Also, note that using procedure names beginning with sp_ is usually reserved
for system stored procedures, and isn't recommended for application code.

Simon