SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create procedure for data modification


Create procedure for data modification

Author
Message
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 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.
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2077 Visits: 1721
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




 
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218205 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63570 Visits: 17966
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.

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)
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 84
THANK YOU VERY MUCH.
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 84
THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63570 Visits: 17966
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 Modens 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)
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2077 Visits: 1721
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.

 
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 84
Yes, right you are, I will certainly learn the tally method for making life much easier and thank you very much.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search