Create procedure for data modification

  • table name : mrinf

    fields : companycode nchar(4), mrno (nchar(9), chqno nvarchar (50), tranamount decmal (18,2)

    I have to update the several sequential (incremented by 1) mrno to a new squential mrno (incremented by 1)

    therefore i have written a stored procedure

    CREATE PROCEDURE dbo.changemr (@counter int, @prevmrno nchar(9), @newmrno nchar (9))

    AS

    BEGIN

    SET NOCOUNT ON;

    [highlight=#ffff11]Declare @count int

    select @count=1

    while @count=@counter

    select @count = @count+1

    select @prevmrno=@prevmrno+1

    update mrinf set mrno=@newmrno where comcod=3305 and mrno=@prevmrno

    END

    GO[/highlight]

    but it is not giving the desired result.

    For example I am executing the procedure by

    use databasename

    exec dbo.mrchange 4, '000053345', '000001881'

    result should be like this

    Previous MR No and new MR NO

    000053345 000001881

    000053346 000001882

    000053347 000001883

    000053348 000001884

    but not getting any result no change in the table. Can anybody help me to solve this problem.

  • This should do what you need. BUT please, read this article and work on getting rid of the WHILE loop.

    The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

    CREATE PROCEDURE dbo.changemr

    @counter INT

    ,@prevmrno NCHAR(9)

    ,@newmrno NCHAR(9)

    ,@comcod INT

    AS

    BEGIN

    /*

    EXEC dbo.changemr 4, '000053345', '000001881', 3305

    */

    SET NOCOUNT ON

    DECLARE

    @intCount INT

    ,@intPrev INT

    ,@intNew INT

    ,@strPrev NCHAR(9)

    ,@strNew NCHAR(9)

    SET @intCount = 1

    WHILE @intCount <= @counter

    BEGIN

    SET @intPrev = CAST(@prevmrno AS INT)+1

    SET @intNew = CAST(@newmrno AS INT)+1

    SET @strPrev = REPLICATE('0',9-LEN(@intPrev))+CAST(@intprev AS NVARCHAR(10))

    SET @strNew = REPLICATE('0',9-LEN(@intNew))+CAST(@intNew AS NVARCHAR(10))

    UPDATE mrinf

    SET mrno = @newmrno

    WHERE

    comcod = @comcod

    AND mrno = @prevmrno

    SET @prevmrno = @strPrev

    SET @newmrno = @strNew

    SET @intCount = @intCount + 1

    END

    END

     

  • Rauf Miah (6/23/2013)


    table name : mrinf

    fields : companycode nchar(4), mrno (nchar(9), chqno nvarchar (50), tranamount decmal (18,2)

    I have to update the several sequential (incremented by 1) mrno to a new squential mrno (incremented by 1)

    therefore i have written a stored procedure

    CREATE PROCEDURE dbo.changemr (@counter int, @prevmrno nchar(9), @newmrno nchar (9))

    AS

    BEGIN

    SET NOCOUNT ON;

    [highlight=#ffff11]Declare @count int

    select @count=1

    while @count=@counter

    select @count = @count+1

    select @prevmrno=@prevmrno+1

    update mrinf set mrno=@newmrno where comcod=3305 and mrno=@prevmrno

    END

    GO[/highlight]

    but it is not giving the desired result.

    For example I am executing the procedure by

    use databasename

    exec dbo.mrchange 4, '000053345', '000001881'

    result should be like this

    Previous MR No and new MR NO

    000053345 000001881

    000053346 000001882

    000053347 000001883

    000053348 000001884

    but not getting any result no change in the table. Can anybody help me to solve this problem.

    How many iterations do you think you'll have with this controlling the WHILE loop?

    while @count=@counter

    According to your example, 0. Go back an look at your code. Step through it with your brain. You'll be able to figure it out. It's just a loop.

    Once you've done that, come back and post the code you have working. Then we'll show you how to really simplify this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • use astrealerpdba

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE dbo.changemr

    @counter int

    ,@prevmrno nchar(9)

    ,@newmrno nchar (9)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @count int

    SET @count=1

    WHILE @count<=@counter

    SET @prevmrno=@prevmrno+1

    SET @newmrno =@newmrno +1

    UPDATE mrinf set mrno=@newmrno where comcod=3305 and mrno=@prevmrno

    SET @count = @count+1

    END

    GO

  • As Jeff and Steve have eluded, you don't need a loop to do this. You just need to use a tally table.

    Something like this should be close.

    update m set mrno = mrno + N

    from #mrinf m

    join Tally t on t.N >= @prevmrno and t.N <= @newmrno

    where comcod = 3305

    I would suggest that you change your datatypes to ints instead of varchar so you don't have to continuously wrestle with varchar data and converting/casting back and forth.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • THANK YOU VERY MUCH.

  • THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.

  • Rauf Miah (6/24/2013)


    THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.

    Then take some time and read the article that Steven linked to above. In case you missed it here it is again.

    http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Rauf Miah (6/24/2013)


    THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.

    Especially if one spent most of their formative years programming with linear-based languages like php or classic ASP as I did, getting rid of a looping mindset is difficult. For a long time I wrote SQL code as if it was just another version of ASP.

    It took a lot of effort and re-writes and PRACTICE to learn that good SQL coding is SET based. That means every query retrieves the data as a SET (i.e., table) and not just a collection of rows that must be traversed "row-by-agonizing-row" (RBAR as Jeff Moden calls it).

    The best way to turn your mindset around on this will be to take some of your simpler code that contains a WHILE loop or CURSOR loop and using examples from the dozens of posts and articles here on SQLServerCentral, work on making use of a tally table to eliminate the traditional loop. It's a hard concept to visualize at first, but keep practicing. And especially on any new work, when it looks like you need to write a loop, just say "No!" and work on a set based method. This may slow down your development time at first, but eventually it will come together in your brain and you will find it hard to think any other way.

     

  • Yes, right you are, I will certainly learn the tally method for making life much easier and thank you very much.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply