说明:
表tb_Ref_key2know是一个关联表,存储的是2种id
表tb_Knowledge 是条目表 有字段Knowledge_id
表tb_Keyword 是关键字表 有字段Keyword_id,Keyword_name要做的事情:通过存储过程向tb_Ref_key2know插入数据,通过Keyword_name查询到Keyword_id给参数,其中在程序中通过数组赋值的。CREATE PROCEDURE Proc_Insert_Ref_key2know
(/*声明存储过程的参数*/
@Keyword_name varchar(20),
@Knowledge_id int
)
AS
/*关键字名称数量*/
DECLARE @Keyword_name_count as intSET @Keyword_name_count=
(
SELECT Count(*) FROM tb_Keyword WHERE Keyword_name=@Keyword_name
)
/*如果关键字不存在,插入到关键字和知识条目关联表中*/
IF @Keyword_name_count<=0
BEGIN
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@Identity
END
GO
表tb_Ref_key2know是一个关联表,存储的是2种id
表tb_Knowledge 是条目表 有字段Knowledge_id
表tb_Keyword 是关键字表 有字段Keyword_id,Keyword_name要做的事情:通过存储过程向tb_Ref_key2know插入数据,通过Keyword_name查询到Keyword_id给参数,其中在程序中通过数组赋值的。CREATE PROCEDURE Proc_Insert_Ref_key2know
(/*声明存储过程的参数*/
@Keyword_name varchar(20),
@Knowledge_id int
)
AS
/*关键字名称数量*/
DECLARE @Keyword_name_count as intSET @Keyword_name_count=
(
SELECT Count(*) FROM tb_Keyword WHERE Keyword_name=@Keyword_name
)
/*如果关键字不存在,插入到关键字和知识条目关联表中*/
IF @Keyword_name_count<=0
BEGIN
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@Identity
END
GO
(/*声明存储过程的参数*/
@Keyword_name varchar(20),
@Knowledge_id int
)
AS
/*关键字名称数量*/
DECLARE @Keyword_name_count as intSET @Keyword_name_count=
(
SELECT Count(*) FROM tb_Keyword WHERE Keyword_name=@Keyword_name
)
/*如果关键字不存在,插入到关键字和知识条目关联表中*/
IF @Keyword_name_count<=0
BEGIN INSERT INTO tb_Keyword
(Keyword_name) values (@Keyword_name)
set @id=@@Identity INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
values(@id,@Knowledge_id)
RETURN @@Identity
END
else
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@IdentityGO
(/*声明存储过程的参数*/
@Keyword_name varchar(20),
@Knowledge_id int
)
AS
/*关键字名称数量*/
DECLARE @Keyword_name_count as intSET @Keyword_name_count=
(
SELECT Count(*) FROM tb_Keyword WHERE Keyword_name=@Keyword_name
)
declare @id int
/*如果关键字不存在,插入到关键字和知识条目关联表中*/
IF @Keyword_name_count<=0
BEGIN INSERT INTO tb_Keyword
(Keyword_name) values (@Keyword_name)
set @id=@@Identity INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
values(@id,@Knowledge_id)
RETURN @@Identity
END
else
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@IdentityGO
else
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@IdentityGO为什么还要执行这么一段代码呢? 如果记录已经存在 就不做操作了
麻烦给讲一下。
再次感谢你,谢谢!CREATE PROCEDURE Proc_Insert_Ref_key2know
(/*声明存储过程的参数*/
@Keyword_name varchar(20),
@Knowledge_id int
)
AS
DECLARE @Keyword_name_count as intSET @Keyword_name_count=
(
SELECT Count(*)
FROM tb_Ref_key2know
WHERE Keyword_id= (select keyword_id from tb_keyword where keyword_name = @Keyword_name )
and knowledge_id = @Knowledge_id
)
IF @Keyword_name_count<=0
BEGIN
INSERT INTO tb_Ref_key2know
(Keyword_id,
Knowledge_id)
SELECT Keyword_id,@Knowledge_id FROM tb_Keyword
WHERE Keyword_name = @Keyword_name
RETURN @@Identity
END
GO