如何编写动态SQL查询以使用适当的MasterLevelId从表中复制项目

问题描述

我有一个包含预算项目的预算表.每个项目都有一个ID(自动递增)，BudgetId，BudgetItemLevel(某种级别结构)，BudgetItemCode(非唯一)，BudgetItemName(非唯一)，BudgetItemMasterID(与上级BudgetItem的连接)，BudgetItemValue，BudgetItemStatus和某些DateTime值，如下所示...

I have a Budget table containing budget items. Each item has na ID (Autoincrement), BudgetId, BudgetItemLevel (some kind of level structure), BudgetItemCode(Not unique), BudgetItemName(not unique), BudgetItemMasterID (Connection to superior BudgetItem), BudgetItemValue, BudgetItemStatus and some DateTime values as shown bellow...

每个预算可以具有自定义的级别数(BudgetId = 1-> 2个级别； BudgetId = 2-> 3个级别，依此类推...).BudgetItemMasterId = NULL的BudgetItems是顶级项目...

Each Budget can have a custom number of levels (BudgetId=1 -> 2 Levels; BudgetId=2 -> 3 levels and so on...).BudgetItems with the BudgetItemMasterId = NULL are top level items...

我需要做的是复制一个Budget及其项目(与原始项目具有完全相同的值)，并将BudgetId值传递给StoredProcedure.困难的部分是为新的复制的预算项目

What i need to do is to copy a Budget with its items(with exact same values as original) passing a BudgetId value to StoredProcedure. The difficult part here is to get the right BudgetMasterId for the new copied Budget items

示例

如果我复制BudgetId = 1，则新预算应如下所示:

If i copy the BudgetId = 1, the new budget should look like this:

推荐答案

因此，这是最终的，功能完整的sp.谢谢大家的帮助...

So, here is the final, full functional sp. Thank you all for your help...

CREATE PROCEDURE [dbo].[sp_CopyBudgetByBudgetId] 
@BudgetId int
AS
BEGIN
            DECLARE @identity int
            DECLARE @tempId int
            DECLARE @tempValue int

            INSERT INTO T_Budget ([BudgetName],[BudgetMaxLevel],[BudgetCurrency],[BudgetStatus],[BudgetAppliedToDonor],[sysDateTimeCreated],[sysDateTimeModified])
            SELECT [BudgetName],[BudgetMaxLevel],[BudgetCurrency],1,0,GETDATE(),GETDATE()
            FROM T_Budget
            WHERE ID = @BudgetId

            SELECT @identity= @@IDENTITY

            IF(@identity > 0)
            BEGIN
                    INSERT INTO T_BudgetCharts2([BudgetId],[BudgetItemLevel],[BudgetItemCode],[BudgetItemName],[BudgetItemMasterID],[BudgetItemBudegtValue],[BugedtItemStatus], [BugedtIdOriginal], [MasterIdOriginal],[sysDateTimeCreated],[sysDateTimeModified])
                    SELECT @identity,[BudgetItemLevel],[BudgetItemCode],[BudgetItemName],[BudgetItemMasterID],[BudgetItemBudegtValue],[BugedtItemStatus], [ID],[BudgetItemMasterID], GETDATE(),GETDATE()
                    FROM T_BudgetCharts2
                    WHERE BudgetId = @BudgetId

                    Declare @ID int
                    DECLARE IDs CURSOR LOCAL FOR (select ID from T_BudgetCharts2 where BudgetId = @identity)

                    OPEN IDs
                    FETCH NEXT FROM IDs into @ID

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                            If (Select MasterIdOriginal From T_BudgetCharts2 Where ID = @ID) IS NOT NULL
                                BEGIN
                                SET @tempId = (Select MasterIdOriginal From T_BudgetCharts2 Where ID = @ID)
                                SET @tempValue = (Select ID FROM T_BudgetCharts2 WHERE BugedtIdOriginal = @tempId) 
                                Update T_BudgetCharts2 SET BudgetItemMasterId = @tempValue WHERE ID = @ID
                            END
                            FETCH NEXT FROM IDs into @ID
                    END

                    CLOSE IDs
                    DEALLOCATE IDs
            END
END

这篇关于如何编写动态SQL查询以使用适当的MasterLevelId从表中复制项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！