CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
A.Goutam
A.Goutam

Reputation: 3494

Order by in varchar

in my table the value is like this in field transectionNO is like R1000891 R1000892 R1000893 ..... I want that last transactionNo means R1000893 as a result without R . So i can add R + last transactionNo + 1 in to create new transectionNo.

i used below query but sometime this query gives old value so it is creation error. 

 DECLARE @trnsectionNo varchar(50) ,   
             @RoundTotal varchar(50) ,  
             @transactionNo varchar(50)   

 SELECT  top 1  @trnsectionNo = trans_no from Item_Receive ORDER BY trans_no DESC   

    SET @trnsectionNo= Substring(@trnsectionNo,2,10)   
    SET  @RoundTotal=  (ISNULL(@trnsectionNo,0)  + 1)  
    SET @transactionNoNew  = 'R'+@RoundTotal

thanks for your help .

Upvotes: 1

Views: 102

Answers (4)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Please try this query. I've also created a fiddle for demo: http://sqlfiddle.com/#!6/e928b/6

Note that changes I made only included casting the substring as INT and applying MAX on it.

DECLARE @trnsectionNo INT ,   
             @RoundTotal varchar(50) ,  
             @transactionNoNew  varchar(50)   

 SELECT   @trnsectionNo = MAX(CAST(Substring(@trnsectionNo,2,10)AS INT))  from Item_Receive 

    SET  @RoundTotal=  (ISNULL(@trnsectionNo,0)  + 1)  
    SET @transactionNoNew  = 'R'+@RoundTotal

Upvotes: 1

beercohol
beercohol

Reputation: 2587

I'm writing this as an answer as it's too long for a comment.

The other answers here will tell you how to fix your problem by converting your varchar to an int. They're correct within the scope of the question you asked, which in essence was "how do I make this piece of really unsafe code work (most of the time)?"

Why do I say your code is unsafe? Atomicity! If you have two calls to your query close together you will get duplicate numbers. Please read about Atomicity and related concepts here: http://searchsqlserver.techtarget.com/definition/ACID They are critical for writing safe DB Code!

If you want to continue with the code you have, and give yourself a hard life trying to make it safe, you would need to wrap the whole thing in a transaction and hold a lock on your table too. The transaction would also need to include the subsequent insert of your new ID number, which you don't include in your snippet.

But why use a varchar to hold an sequential transaction No. anyway? If you need it to show with a "R" in front that should be in your UI code.

If you want safe code with atomicity and an easy life, just use an int column with the identity attribute. See here for more info: http://www.sqlteam.com/article/understanding-identity-columns

If you need to get the new ID value back to your app, use the SCOPE_IDENTITY() function: https://msdn.microsoft.com/en-gb/library/ms190315(v=sql.110).aspx

Upvotes: 0

A_Sk
A_Sk

Reputation: 4630

Try Using Stuff 

select top 1  @trnsectionNo = trans_no from Item_Receive order by Stuff(trans_no,1,1,'') desc

Upvotes: 0

James Z
James Z

Reputation: 12317

The problem you have is that you're not converting the number into int, so SQL Server can't do the +1. This is what you should be doing:

DECLARE @trnsectionNo varchar(50) ,   
        @transactionNoNew varchar(50)

SELECT  top 1  @trnsectionNo = trans_no from Item_Receive ORDER BY trans_no DESC   

if (@@rowcount = 0) set @trnsectionNo = 'R0'

select @transactionNoNew = 'R'+convert(varchar(10), convert(int, Substring(@trnsectionNo,2,10)) + 1)

If you need to have zeros in the number, e.g. R000001 instead of R1, then you'll need to add more logic.

Upvotes: 1

Related Questions