June 23, 2013 at 12:33 am
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.
June 23, 2013 at 8:20 pm
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
June 23, 2013 at 9:02 pm
Rauf Miah (6/23/2013)
table name : mrinffields : 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
Change is inevitable... Change for the better is not.
June 23, 2013 at 10:22 pm
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
June 24, 2013 at 7:46 am
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/
June 24, 2013 at 9:09 am
THANK YOU VERY MUCH.
June 24, 2013 at 9:14 am
THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.
June 24, 2013 at 9:23 am
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/
June 24, 2013 at 9:48 am
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.
June 25, 2013 at 5:53 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply