尝试使用程序员逻辑在 SQL 服务器上编写存储过程但不起作用

2021-09-10 00:00:00 sql tsql sql-server stored-procedures

好的，我的表 - SQL Server 2014

Ok here my table - SQL server 2014

假设我有以下变量

Computer Hardware > Video Cards

所以计算机硬件是父节点，显卡是子节点

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 服务器给出的错误

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

最后我的问题是如何使这个存储过程工作?我的逻辑很好，但是我找不到将其转换为 SQL 逻辑的方法

在这种情况下转换为 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

相关文章