循环在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 @hdnIncomeIDNOFirstly, 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屋!