嵌套存储过程

发布于 2024-12-12 06:18:15 字数 768 浏览 0 评论 0原文

我需要创建一个存储过程，它将返回代码列表，然后我需要调用另一个存储过程来逐个检查每个代码。

我该怎么做？

CREATE PROCEDURE [dbo].[paBltBuscarBoletasASA] @id_Asa int  
AS
DECLARE @Query int, @Contador int 
SET @Contador = 0
BEGIN
      SET NOCOUNT ON;  
      SET @Query = (
                SELECT 
                    localizacion.c_Fk_IdBoleta
                FROM 
                    Blt_Boleta as boleta, Fnc_Localizacion as localizacion
                WHERE 
                    boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND
                    localizacion.si_CodAsa = @id_Asa) //This query give the list of Codes. For example 45550711, 40480711, 80110711... etc

    exec dbo.paBltMarcarErroresBoleta @Query //And here I need send one by one that list of Codes
END

原文

I need create a stored proc that will return a list of a code, and then I need to call another stored proc to review each code, one by one.

How can I do this?

CREATE PROCEDURE [dbo].[paBltBuscarBoletasASA] @id_Asa int  
AS
DECLARE @Query int, @Contador int 
SET @Contador = 0
BEGIN
      SET NOCOUNT ON;  
      SET @Query = (
                SELECT 
                    localizacion.c_Fk_IdBoleta
                FROM 
                    Blt_Boleta as boleta, Fnc_Localizacion as localizacion
                WHERE 
                    boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND
                    localizacion.si_CodAsa = @id_Asa) //This query give the list of Codes. For example 45550711, 40480711, 80110711... etc

    exec dbo.paBltMarcarErroresBoleta @Query //And here I need send one by one that list of Codes
END

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

⒈起吃苦の倖褔 2024-12-19 06:18:15

您可以考虑添加标量函数并在查询中调用它，例如：

SELECT 
    localizacion.c_Fk_IdBoleta,
    dbo.checkCode(localizacion.c_Fk_IdBoleta) as Check
FROM 
    Blt_Boleta as boleta, Fnc_Localizacion as localizacion
WHERE 
    boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND
    localizacion.si_CodAsa = @id_Asa

You may consider adding an scalar function and call it in your query, like:

SELECT 
    localizacion.c_Fk_IdBoleta,
    dbo.checkCode(localizacion.c_Fk_IdBoleta) as Check
FROM 
    Blt_Boleta as boleta, Fnc_Localizacion as localizacion
WHERE 
    boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND
    localizacion.si_CodAsa = @id_Asa

回复收藏 0 原文

三五鸿雁 2024-12-19 06:18:15

为您设置为等于 @Query 的查询声明 CURSOR，然后在 WHILE @@FETCH_STATUS = 0 循环中将每个后续值插入到变量中。然后将 @Query 变量传递给第二个存储过程，就像您当前所做的那样。下面是一个示例：

DECLARE myCursor CURSOR FOR
SELECT localizacion.c_Fk_IdBoleta
FROM Blt_Boleta as boleta, Fnc_Localizacion as localizacion
WHERE boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND 
localizacion.si_CodAsa = @id_Asa

OPEN myCursor

FETCH NEXT FROM myCursor INTO @Query

WHILE @@FETCH_STATUS = 0
BEGIN
    exec dbo.paBltMarcarErroresBoleta @Query
    //do additional processing

FETCH NEXT FROM myCursor INTO @Query
END

CLOSE myCursor
DEALLOCATE myCursor

其他光标帮助：http://msdn.microsoft.com/ en-us/library/ms180169.aspx

Declare a CURSOR for the query that you are setting equal to @Query, and then insert into the variable each subsequent value in a WHILE @@FETCH_STATUS = 0 loop. Then pass the @Query variable to the second stored procedure as you are currently doing. Here is an example:

DECLARE myCursor CURSOR FOR
SELECT localizacion.c_Fk_IdBoleta
FROM Blt_Boleta as boleta, Fnc_Localizacion as localizacion
WHERE boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND 
localizacion.si_CodAsa = @id_Asa

OPEN myCursor

FETCH NEXT FROM myCursor INTO @Query

WHILE @@FETCH_STATUS = 0
BEGIN
    exec dbo.paBltMarcarErroresBoleta @Query
    //do additional processing

FETCH NEXT FROM myCursor INTO @Query
END

CLOSE myCursor
DEALLOCATE myCursor

Additional cursor help: http://msdn.microsoft.com/en-us/library/ms180169.aspx

回复收藏 0 原文

~没有更多了~