Stored Procedure Will Not Delete Records

[FrankPV]

I'm running a stored procedure which attempts to delete records that are greater than an order number which is selected in the previous line and stored in a variable. The delete line does not delete any records and I don't understand why. Any help would be appreciated. The relevant code is below. The Convert commands were added as an attempt to make it work but it made no difference.

DECLARE @LastOrderNumber nvarchar(8)


SELECT @LastOrderNumber = ORDERNUM from ZORDERMASTER WHERE (NOT (TRANCODE = N'SPEV')) AND (NOT (TRANCODE = N'PKG')) order by ORDERNUM


DELETE FROM ZORDERMASTER WHERE (CONVERT(numeric, ORDERNUM) > CONVERT (numeric, @LastOrderNumber))

 

[mrdenny]

First you need to specify the size of the numeric range that you are using (12,0) (18,2), etc. If all the numbers are whole numbers considder using INT or BIGINT.

Is the data not stored in a numeric field?



Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[FrankPV]

The data is stored in an nvarchar type field length of 8. I will try converting to int rather than numeric to see if that works. The confusing thing is I didn't have to do any conversions to select the records I needed using the ordernum field later in the stored procedure.