loop in a SQL stored procedure

  • Thread starter Thread starter iHavAQuestion
  • Start date Start date
I

iHavAQuestion

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
 
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
 
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)
 
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.
 
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
 
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
 
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
Click to expand...

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
 
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.
Click to expand...

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
 
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.
 
I challenge you to back up the statement that I have a serious issue with
anyone who disagrees with me.
Click to expand...

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.
 
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.
Click to expand...

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 eyes> comment 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.
Click to expand...

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
 
Even though I thought Mark's comments about Access not having a database,
Click to expand...

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,
Click to expand...

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,
Click to expand...

I don't agree. And you can obviously post correctly when you want to.
 
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.
Click to expand...

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.
 
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!
 
Back
Top