Trying to compose a Stored Procedure at SQL server

问题:

Ok here my table - SQL server 2014

Assume that i have the following variables

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

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

So finally my question how do make this stored procedure work ? My logic is fine however i can't find a way to turn it into SQL logic

When converted to if else logic in that case assignments are giving error

回答1:

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

回答2:

  You missed set before cl_InsertedId  - or u also use Scope_Identity()



  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)


    if @cl_Parent_Node_Exists_CategoryId = 0 

    begin

    insert into tblProductCategories (cl_CategoryName,cl_ParentCategoryId)
    set @cl_InsertedId = INSERTED.cl_CategoryId
    values (@cl_CategoryName,@cl_ParentCategoryId)
    end
    else
    begin
    set @cl_InsertedId = @cl_Parent_Node_Exists_CategoryId

    end

    end