循环在SQL存储过程中 [英] loop in a SQL stored procedure

问题描述

我需要在变量@ hdnIncomeIDNO1和

@ IncomeType_CODE11的存储过程中循环到@hdnIncomeIDNO10和@ IncomeType_CODE10


而不是编写upadate声明10次（正如我在下面所做的那样），有没有

我可以循环@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

解决方案

" iHavAQuestion" < iH *********** @ discussion.microsoft.com写信息

新闻：2A ***************** ***************** @ microsof t.com ...

而不是写10次upadate语句（正如我在下面所做的那样），有没有

我可以循环@hdnIncomeIDNO

首先，这不是严格意义上的ASP.NET问题本身 - 你可能会得到一个SQL Server新闻组中更好的响应...


无论如何，如果你需要要在SQL Server中执行此操作，您需要

调查动态SQL和sp_executesql - 打开SQL BOL并进行搜索

for sp_executesql ...


至于循环，像WHILE ...... BREAK ...... CONTINUE可能会这样做 - $
诀窍 - 再次，它全部都在BOL中。 />
-

Mark Rae

ASP.NET MVP
http://www.markrae.net

这是一个在sql中循环的简短例子。

声明@ var1 int

声明@ var2 int


设置@ var1 = 10

设置@ var2 = 1

而@ var2< @ var1

开始

设置@ var2 = @ var2 + 1

print''现在位于：''+ cast（@ var2 as char ）

结束

2008年6月12日星期四11:39:02 -0300，iHavAQuestion

< iH ****** *****@discussions.microsoft.comwrote:

我需要在变量@ hdnIncomeIDNO1和

的存储过程中循环@ IncomeType_CODE11到@hdnIncomeIDNO10和@ IncomeType_CODE10


而不是写10次upadate语句（如下所示），是

那里

我可以循环@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

-

使用Opera革命性的电子邮件客户端： http://www.opera.com/mail/

sql不支持循环直播传递参数，也没有像c＃那样的

数组参数。更好的方法是使用数据传递xml参数

。然后你可以使用xml进行设置操作，或者如果你想一次处理一个语句，则循环通过

xml。


声明@xml xml

设置@xml =''

< params>

< param>< id> 1< / id><值> hello< / value>< / param>

< param>< id> 2< / id>< value> bye< / value>

< / param>

< / params>

''

UPDATE ClientIncome_T1

SET IncomeType_CODE = value
来自ClientIncome_T1的


加入（

选择

params.id.query（''id''）。值（''。''，''varchar（50）''）为id，

params.id.query（''value''）。value（''。''，' 'varchar（50）''）作为价值

来自@ xml.nodes（''// param''）as params（id））

）p on ClientIncome_IDNO = id

- 布鲁斯（sqlwork.com）

" iHavAQuestion"写道：

我需要在变量@ hdnIncomeIDNO1和

@ IncomeType_CODE11的存储过程中循环到@ hdnIncomeIDNO10和@ IncomeType_CODE10


而不是写10次upadate语句（正如我在下面所做的那样），有没有

我可以循环@hdnIncomeIDNO


DECLARE @IncomeType_CODE CHAR（5）

BEGIN

SET @ IncomeType_CODE = @ IncomeType_CODE1

更新ClientIncome_T1

SET

IncomeType_CODE = @ IncomeType_CODE，

WHERE

ClientIncome_IDNO = @ hdnIncomeIDNO1

END

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

解决方案

"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

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/

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

这篇关于循环在SQL存储过程中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！