日期:2014-05-17  浏览次数:20680 次

写一个储存过程!! 求思路!!!
现有两张表, Book(ID, Book_name), Keyword(Book_name,keyword,weight)

要求写一个存储过程向两张表输入数据,数据格式为(Book_name, 'K1,W1,K2,W2...Kn,Wn') ,如果Book表中不存在Book_name,那么要先往Book表中加入该Book_name,然后再把数据加入Keyword表。

Kn=Keyword,Wn=weight

我写了以下代码,但是不符合要求,哪位大哥帮帮改改啊,主要是输入的数据,应该怎么样分拆?

CREATE PROCEDURE insert_procedure
@T_name VARCHAR(35),
@Kword VARCHAR(15),
@Weight NUMERIC(2,1)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM Topic WHERE Topic_name =@T_name )
BEGIN
DECLARE @n INT, @T_ID INT 
SELECT @n=COUNT(Topic_ID) FROM Topic 
SET @T_ID =@n +1
INSERT INTO Topic VALUES (@T_ID ,@T_name )
INSERT INTO Keyword VALUES (@T_name ,@Kword ,@Weight )
END
ELSE
INSERT INTO Keyword VALUES (@T_name ,@Kword ,@Weight )
END


------解决方案--------------------
SQL code

 alter  FUNCTION [dbo].[StringSplit]
     (
       @String VARCHAR(MAX) ,
       @Spliter VARCHAR(5)
     )
 RETURNS @Tb TABLE
     (
       Keyword VARCHAR(45) ,
       Weight VARCHAR(MAX)--NUMERIC(2, 1)
     )
 AS 
     BEGIN  
         DECLARE @Index INT ,
             @One VARCHAR(45) ,
             @Two VARCHAR(max)--NUMERIC(2, 1)
 
         SET @Index = CHARINDEX(@Spliter, @String)  
         WHILE ( @Index > 0 ) 
             BEGIN
                 SET @One = LEFT(@String, @Index-1 )  
                 SET @String = RIGHT(@String, LEN(@String) - @Index)
                 SET @Index = CHARINDEX(@Spliter, @String)  
                 SET @Two = CAST(LEFT(@String, @Index-1 ) AS varchar(max))
 
                 INSERT  INTO @TB
                         ( Keyword, Weight )
                 VALUES  ( @One, @Two )  
                 SET @String = RIGHT(@String, LEN(@String) - @Index)
                 SET @Index = CHARINDEX(@Spliter, @String)  
             END
 
         RETURN  
     END