Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create procedure for data modification Expand / Collapse
Author
Message
Posted Sunday, June 23, 2013 12:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
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.

Post #1466502
Posted Sunday, June 23, 2013 8:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


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


 
Post #1466558
Posted Sunday, June 23, 2013 9:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466560
Posted Sunday, June 23, 2013 10:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
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
Post #1466571
Posted Monday, June 24, 2013 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466745
Posted Monday, June 24, 2013 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
THANK YOU VERY MUCH.
Post #1466786
Posted Monday, June 24, 2013 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.
Post #1466788
Posted Monday, June 24, 2013 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466793
Posted Monday, June 24, 2013 9:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1466800
Posted Tuesday, June 25, 2013 5:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
Yes, right you are, I will certainly learn the tally method for making life much easier and thank you very much.
Post #1467410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse