loop in a SQL stored procedure

[=?Utf-8?B?aUhhdkFRdWVzdGlvbg==?=]

I need to loop in a stored procedure for the variable @hdnIncomeIDNO1 and
@IncomeType_CODE11 to @hdnIncomeIDNO10 and @IncomeType_CODE10

Instead of writing the upadate statement 10 time(as I did below), Is there
any way where I can loop the @hdnIncomeIDNO
DECLARE @IncomeType_CODE CHAR(5)
BEGIN
SET @IncomeType_CODE=@IncomeType_CODE1
UPDATE ClientIncome_T1
SET
IncomeType_CODE=@IncomeType_CODE,
WHERE
ClientIncome_IDNO=@hdnIncomeIDNO1
END

[Mark Rae [MVP]]

"iHavAQuestion" <iH***********@discussions.microsoft.comwrote in message
news:2A**********************************@microsof t.com...

Instead of writing the upadate statement 10 time(as I did below), Is there
any way where I can loop the @hdnIncomeIDNO

Firstly, this isn't strictly an ASP.NET question per se - you'd probably get
a better response in one of the SQL Server newsgroups...

Anyway, if you need to do this all within SQL Server, you will need to
investigate dynamic SQL and sp_executesql - open up SQL BOL and do a search
for sp_executesql...

As for looping, something like WHILE...BREAK...CONTINUE would probably do
the trick - again, it's all in BOL.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[MarkE]

Here's a short example of looping in sql.
declare @var1 int
declare @var2 int

set @var1 = 10
set @var2 = 1
while @var2 < @var1
begin
set @var2 = @var2 + 1
print 'Now at: ' + cast(@var2 as char)
end
On Thu, 12 Jun 2008 11:39:02 -0300, iHavAQuestion
<iH***********@discussions.microsoft.comwrote:

I need to loop in a stored procedure for the variable @hdnIncomeIDNO1 and
@IncomeType_CODE11 to @hdnIncomeIDNO10 and @IncomeType_CODE10

Instead of writing the upadate statement 10 time(as I did below), Is
there
any way where I can loop the @hdnIncomeIDNO
DECLARE @IncomeType_CODE CHAR(5)
BEGIN
SET @IncomeType_CODE=@IncomeType_CODE1
UPDATE ClientIncome_T1
SET
IncomeType_CODE=@IncomeType_CODE,
WHERE
ClientIncome_IDNO=@hdnIncomeIDNO1
END

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[=?Utf-8?B?YnJ1Y2UgYmFya2Vy?=]

sql does not support looping thru passed parameters, nor does it have an
array parameter like c#. a better approach would be to pass an xml parameter
with the data. then you could do a set operation with the xml, or loop thru
the xml if you want to handle a statement at a time.

declare @xml xml
set @xml = '
<params>
<param><id>1</id><value>hello</value></param>
<param><id>2</id><value>bye</value>
</param>
</params>
'
UPDATE ClientIncome_T1
SET IncomeType_CODE=value
from ClientIncome_T1
join (
select
params.id.query('id').value('.','varchar(50)') as id,
params.id.query('value').value('.','varchar(50)') as value
from @xml.nodes('//param') as params(id))
) p on ClientIncome_IDNO=id
-- bruce (sqlwork.com)
"iHavAQuestion" wrote:

I need to loop in a stored procedure for the variable @hdnIncomeIDNO1 and
@IncomeType_CODE11 to @hdnIncomeIDNO10 and @IncomeType_CODE10

Instead of writing the upadate statement 10 time(as I did below), Is there
any way where I can loop the @hdnIncomeIDNO
DECLARE @IncomeType_CODE CHAR(5)
BEGIN
SET @IncomeType_CODE=@IncomeType_CODE1
UPDATE ClientIncome_T1
SET
IncomeType_CODE=@IncomeType_CODE,
WHERE
ClientIncome_IDNO=@hdnIncomeIDNO1
END

[Jordan S.]

RE:
<< again, it's all in BOL >>

Mark, this sort of response is utterly unhelpful, at best, and easily
perceived as condescending. These news groups are one of many resources,
including BOL, google, printed books, etc. Pointing out that the answer is
easily found in an alternative resource usually isn't helpful.

Think about it - you could answer *any* question in a news group like this:
"just google it" or "look it up in BOL" which is really a variation of
"RTFM". These sorts of answers are *not* the stuff of a Microsoft-designated
"MVP".

If you are so incredibly inconvenienced by a question that you feel is
readily available via some other resource, then you could just pass and not
respond at all. That would definitely help the signal-to-noise ratio in this
NG.

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

"iHavAQuestion" <iH***********@discussions.microsoft.comwrote in message
news:2A**********************************@microsof t.com...

>Instead of writing the upadate statement 10 time(as I did below), Is
there
any way where I can loop the @hdnIncomeIDNO

Firstly, this isn't strictly an ASP.NET question per se - you'd probably
get a better response in one of the SQL Server newsgroups...

Anyway, if you need to do this all within SQL Server, you will need to
investigate dynamic SQL and sp_executesql - open up SQL BOL and do a
search for sp_executesql...

As for looping, something like WHILE...BREAK...CONTINUE would probably do
the trick - again, it's all in BOL.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae [MVP]]

"Jordan S." <[e-mail] in message
news:eB**************@TK2MSFTNGP05.phx.gbl...

[top-posting corrected]

RE:
<< again, it's all in BOL >>

Mark, this sort of response is utterly unhelpful, at best, and easily
perceived as condescending.

You're entitled to your opinion, but I don't agree with it...

Firstly, I informed the OP that he'd posted in the wrong newsgroup,
suggesting that he try a more appropriate newsgroup which might get him a
better answer.

Secondly, I then gave him suggestions of what to use in SQL Server to solve
his problem, namely sp_executesql and WHILE...BREAK...CONTINUE.

Finally, I told him where to find more information on this.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Peter Bromberg [C# MVP]]

First I tend to agree with Mark that you are in the wrong group. While his
reply could be considered somewhat condescending to some, he has a point -
people need to learn to help themselves, or they will never acquire the
skills to become professsional developers.

The problem you have is probably one of how to pass delimited strings of one
or more parameters, split the strings into Table variables inside your
stored proc, and iterate over these to perform the multiple inserts. You can
use an easy to find User Defined Function called "fn_Split" to handle the
first problem. However, a discussion of Table Variables, how to create them
with primary keys that enable looping and so on is beyond the scope of a
newsgroup post and will simply require some serious study on your part.
Hence "BOL-RTFM".
Peter

"iHavAQuestion" <iH***********@discussions.microsoft.comwrote in message
news:2A**********************************@microsof t.com...

>I need to loop in a stored procedure for the variable @hdnIncomeIDNO1 and
@IncomeType_CODE11 to @hdnIncomeIDNO10 and @IncomeType_CODE10

Instead of writing the upadate statement 10 time(as I did below), Is there
any way where I can loop the @hdnIncomeIDNO
DECLARE @IncomeType_CODE CHAR(5)
BEGIN
SET @IncomeType_CODE=@IncomeType_CODE1
UPDATE ClientIncome_T1
SET
IncomeType_CODE=@IncomeType_CODE,
WHERE
ClientIncome_IDNO=@hdnIncomeIDNO1
END

[Jonathan Wood]

I'll have to second this sentiment. Reading some of his replies, it looks
like I'm not the only one Mark seems more interesting in arguing with that
trying to be helpful.

Jonathan

"Jordan S." <[e-mail] in message
news:eB**************@TK2MSFTNGP05.phx.gbl...

RE:
<< again, it's all in BOL >>

Mark, this sort of response is utterly unhelpful, at best, and easily
perceived as condescending. These news groups are one of many resources,
including BOL, google, printed books, etc. Pointing out that the answer is
easily found in an alternative resource usually isn't helpful.

Think about it - you could answer *any* question in a news group like
this: "just google it" or "look it up in BOL" which is really a
variation of "RTFM". These sorts of answers are *not* the stuff of a
Microsoft-designated "MVP".

If you are so incredibly inconvenienced by a question that you feel is
readily available via some other resource, then you could just pass and
not respond at all. That would definitely help the signal-to-noise ratio
in this NG.

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

>"iHavAQuestion" <iH***********@discussions.microsoft.comwrote in
message news:2A**********************************@microsof t.com...

>>Instead of writing the upadate statement 10 time(as I did below), Is
there
any way where I can loop the @hdnIncomeIDNO

Firstly, this isn't strictly an ASP.NET question per se - you'd probably
get a better response in one of the SQL Server newsgroups...

Anyway, if you need to do this all within SQL Server, you will need to
investigate dynamic SQL and sp_executesql - open up SQL BOL and do a
search for sp_executesql...

As for looping, something like WHILE...BREAK...CONTINUE would probably do
the trick - again, it's all in BOL.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[siccolo]

On Jun 12, 10:39*am, iHavAQuestion
<[e-mail]:

I need to loop in a stored procedure for the variable @hdnIncomeIDNO1 and
@IncomeType_CODE11 to @hdnIncomeIDNO10 and @IncomeType_CODE10

Instead of writing the upadate statement 10 time(as I did below), Is there
any way where I can loop the @hdnIncomeIDNO

DECLARE @IncomeType_CODE * * *CHAR(5)
BEGIN
* * * * SET @IncomeType_CODE=@IncomeType_CODE1
* * * * * * UPDATE ClientIncome_T1
* * * * * * * * *SET
* * * * * * * * *IncomeType_CODE=@IncomeType_CODE,
* * * * * * WHERE
* * * * * * * * *ClientIncome_IDNO=@hdnIncomeIDNO1
END

if @hdnIncomeIDNO1 contains comma-delimited list of values, then you
can do something like this:

declare @update_command varchar(6666)
set @update_command = ' update ClientIncome_T1' +
' set IncomeType_CODE=' +
@IncomeType_Code +
' where ClientIncome_IDNO in ('
+ @hdnIncomeIDNO1 + ')'
... more at http://www.siccolo.com/articles.asp

[David Jackson]

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:u4**************@TK2MSFTNGP06.phx.gbl...

I'll have to second this sentiment. Reading some of his replies, it looks
like I'm not the only one Mark seems more interesting in arguing with that
trying to be helpful.

I totally disagree with this. I've always found Mark Rae and all the other
MVP's in here to be extremely helpful.

You on the other hand appear to have a serious issue with anyone who
disagrees with you.

DJ

[Jonathan Wood]

I should know better and I have no idea what business this is of yours, but
I challenge you to back up the statement that I have a serious issue with
anyone who disagrees with me.

BTW, my comments here about Mark were confirming someone else's comments
about him. So if you're suggesting it's just me, you're way off.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"David Jackson" <so*****@somewhere.comwrote in message
news:e3****************@TK2MSFTNGP03.phx.gbl...

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:u4**************@TK2MSFTNGP06.phx.gbl...

>I'll have to second this sentiment. Reading some of his replies, it looks
like I'm not the only one Mark seems more interesting in arguing with
that trying to be helpful.

I totally disagree with this. I've always found Mark Rae and all the other
MVP's in here to be extremely helpful.

You on the other hand appear to have a serious issue with anyone who
disagrees with you.

DJ

[David Jackson]

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:uB**************@TK2MSFTNGP06.phx.gbl...

>>I'll have to second this sentiment. Reading some of his replies, it
looks like I'm not the only one Mark seems more interesting in arguing
with that trying to be helpful.

I totally disagree with this. I've always found Mark Rae and all the
other MVP's in here to be extremely helpful.

You on the other hand appear to have a serious issue with anyone who
disagrees with you.

I challenge you to back up the statement that I have a serious issue with
anyone who disagrees with me.

Your thread "XML Database" is a case in point. You started off by saying
that you wanted database functionality for your web site but didn't want to
use a database and asked if an XML file would be a good idea. You were told
that it wasn't a good idea but then got on your high horse because you had
already decided that you were going to use an XML file regardless of whether
anyone suggested a much better alternative.

Then you threw your toys out of the pram and said that you were leaving,
doubtless hoping that someone would beg you to stay. No-one did, and you're
still here.

[Jonathan Wood]

David,

Your thread "XML Database" is a case in point. You started off by saying
that you wanted database functionality for your web site but didn't want
to use a database and asked if an XML file would be a good idea. You were
told that it wasn't a good idea but then got on your high horse because
you had already decided that you were going to use an XML file regardless
of whether anyone suggested a much better alternative.

First off, Stan's helpful comments in that same thread turned out to be
exactly right. He even included potential warnings, which just aren't an
issue in my case.

Even though I thought Mark's comments about Access not having a database, a
database being better than even a simple text file, and the usual comments
about top-down posting were off the mark, everything was cool and I welcomed
his replies. It wasn't until the <rolling eyescomment that I took the
liberty of pointing out that he didn't know my requirements better than I
do.

Then you threw your toys out of the pram and said that you were leaving,
doubtless hoping that someone would beg you to stay. No-one did, and
you're still here.

I'm sorry if you think I should just keep my mouth shut when someone starts
calling me names, but I said I was outta here only after being called names
by someone other than Mark, I meant out of that thread, and there was never
any reason for anyone to ask me to "stay." Besides, ASP.NET is my focus now
so I'll be around. In fact, with time, I may change my own MVP status (yet
again) to ASP.NET.

HTH.

Jonathan

[David Jackson]

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:uO*************@TK2MSFTNGP02.phx.gbl...

>Your thread "XML Database" is a case in point. You started off by saying
that you wanted database functionality for your web site but didn't want
to use a database and asked if an XML file would be a good idea. You were
told that it wasn't a good idea but then got on your high horse because
you had already decided that you were going to use an XML file regardless
of whether anyone suggested a much better alternative.

Even though I thought Mark's comments about Access not having a database,

To be fair, he didn't say that Access doesn't have a database. Quite the
opposite. What he said was that Access isn't a database but rather a tool
for creating database applications, and those database applications use the
Jet database by default. And he's right.

database being better than even a simple text file,

Surely you can't possibly argue that a database is better than a simple text
file for database operations?

and the usual comments about top-down posting were off the mark,

I don't agree. And you can obviously post correctly when you want to.

[Jonathan Wood]

David,

To be fair, he didn't say that Access doesn't have a database. Quite the
opposite. What he said was that Access isn't a database but rather a tool
for creating database applications, and those database applications use
the Jet database by default. And he's right.

What he said was "there's no such thing as an Access database."

At any rate, I'm sorry, but I will speak up when I think someone is being
unfair. And your original characterization of me in the other thread was
neither fair nor accurate.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[David Jackson]

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:un**************@TK2MSFTNGP04.phx.gbl...

>To be fair, he didn't say that Access doesn't have a database. Quite the
opposite. What he said was that Access isn't a database but rather a tool
for creating database applications, and those database applications use
the Jet database by default. And he's right.

What he said was "there's no such thing as an Access database."

Yes he did. And he's right about that too.

[Jonathan Wood]

I'm sorry but I just add to add one final point.

There is a site that has links to people's personal websites at
http://www.edream.org/SiteRSS.aspx?skid=1. Users contribute links to their
own sites and list the customizations they made. One contributer puts the
following as his customizations:

"I built an XML based photo album system that stores the picture meta-data
in an XML file on disk as opposed to the database. This then lets me just
FTP up my pictures."

That user is none other than Scott Guthrie himself. If using XML this way is
good enough for him, maybe I do have a clue to my particular requirements
afterall!

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com
"David Jackson" <so*****@somewhere.comwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:un**************@TK2MSFTNGP04.phx.gbl...

>>To be fair, he didn't say that Access doesn't have a database. Quite the
opposite. What he said was that Access isn't a database but rather a
tool for creating database applications, and those database applications
use the Jet database by default. And he's right.

What he said was "there's no such thing as an Access database."

Yes he did. And he's right about that too.