尝试使用程序员逻辑在 SQL 服务器上编写存储过程但不起作用
好的,我的表 - 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
相关文章