問題描述

好的，我的表 - SQL Server 2014

Ok here my table - SQL server 2014

假設(shè)我有以下變量

Computer Hardware > Video Cards

所以計算機(jī)硬件是父節(jié)點，顯卡是子節(jié)點

So Computer Hardware is being parent node and Video Cards is being children

這里是我的存儲過程，它是由程序員邏輯編寫但不起作用

Here my stored procedure which is written by programmer logic but not working

Create Procedure insert_Product_Category_With_Parent_Node_Key_pair
(
@cl_CategoryName nvarchar(200),
@cl_ParentCategoryName nvarchar(200),
@cl_InsertedId int Output
)
As
Begin

declare @cl_ParentCategoryId int

declare @cl_Parent_Node_Exists_CategoryId int

set @cl_ParentCategoryId = ISNULL((select cl_CategoryId 
from tblProductCategories 
where cl_CategoryName=@cl_ParentCategoryName),0)

set @cl_Parent_Node_Exists_CategoryId = ISNULL((select cl_CategoryId 
from tblProductCategories 
where cl_CategoryName=@cl_CategoryName 
and 
cl_ParentCategoryId=@cl_ParentCategoryId),0)


CASE

when @cl_Parent_Node_Exists_CategoryId = 0 

then

insert into tblProductCategories (cl_CategoryName,cl_ParentCategoryId)
@cl_InsertedId = INSERTED.cl_CategoryId
values (@cl_CategoryName,@cl_ParentCategoryId)

else

@cl_InsertedId = @cl_Parent_Node_Exists_CategoryId

end

end

SQL 服務(wù)器給出的錯誤

The error SQL server giving

    Msg 156, Level 15, State 1, Procedure insert_Product_Category_With_Parent_Node_Key_pair, Line 17
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Procedure insert_Product_Category_With_Parent_Node_Key_pair, Line 22
Incorrect syntax near '@cl_InsertedId'.

好的，我希望它如何工作

Ok here how i want it work

declare @returned_product_categoryId int
exec insert_Product_Category_With_Parent_Node_Key_pair
    @cl_CategoryName = 'Video Cards',
    @cl_ParentCategoryName = 'Computer Hardware'
    @returned_product_categoryId = @cl_InsertedId output
select @returned_product_categoryId

最后我的問題是如何使這個存儲過程工作?我的邏輯很好，但是我找不到將其轉(zhuǎn)換為 SQL 邏輯的方法

在這種情況下轉(zhuǎn)換為 if else 邏輯時，賦值會出錯

推薦答案

Create Procedure insert_Product_Category_With_Parent_Node_Key_pair
(
    @cl_CategoryName nvarchar(200),
    @cl_ParentCategoryName nvarchar(200),
    @cl_InsertedId int Output
)
AS
BEGIN
   SET NOCOUNT ON;

declare @cl_ParentCategoryId int;
declare @cl_Parent_Node_Exists_CategoryId int;

set @cl_ParentCategoryId = ISNULL((select cl_CategoryId 
                                    from tblProductCategories 
                                    where cl_CategoryName = @cl_ParentCategoryName),0)

set @cl_Parent_Node_Exists_CategoryId = ISNULL((select cl_CategoryId 
                                                from tblProductCategories 
                                                where cl_CategoryName = @cl_CategoryName 
                                                and 
                                                cl_ParentCategoryId = @cl_ParentCategoryId),0)



     IF (@cl_Parent_Node_Exists_CategoryId = 0)
      BEGIN
            insert into tblProductCategories (cl_CategoryName,cl_ParentCategoryId)
            values (@cl_CategoryName,@cl_ParentCategoryId)

            SET @cl_InsertedId = SCOPE_IDENTITY();
       END
     ELSE
      BEGIN
        SET @cl_InsertedId = @cl_Parent_Node_Exists_CategoryId;
      END
END

這篇關(guān)于嘗試使用程序員邏輯在 SQL 服務(wù)器上編寫存儲過程但不起作用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！