现有两张表, 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
@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
book_name,k1,w1
book_name,k2,w2
...
这样的格式,再一次性插入即可
k1,w1
k2,w2
...
是输出的表值函数,用逗号一个个的拆分出你要的数
drop table Topic
go
create table Topic(Topic_ID int, Topic_name varchar(35))
insert into Topic
select '1','数学' union all
select '2','语文'if object_id('Keyword')is not null
drop table Keyword
go
create table Keyword(Book_name varchar(35),keyword varchar(15),weight numeric(2,1))
insert into Keyword
select '数学','123456','1.2' union all
select '语文','654321','6.5'select * from Topicselect * from Keyword
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 )
ENDexec insert_procedure '科学','galenkeny','7.8'
CREATE PROCEDURE insert_procedure
@B_name VARCHAR(35),
@Kword VARCHAR(15),
@Weight NUMERIC(2,1)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM Book WHERE Book_name =@B_name )
BEGIN
DECLARE @n INT, @B_ID INT
SELECT @n=COUNT(Topic_ID) FROM Book
SET @T_ID =@n +1
INSERT INTO Book VALUES (@B_ID ,@B_name )
INSERT INTO Keyword VALUES (@B_name ,@Kword ,@Weight )
END
ELSE
INSERT INTO Keyword VALUES (@B_name ,@Kword ,@Weight )
END
这样可不可以
ALTER FUNCTION [dbo].[StringSplit](@String VARCHAR(MAX),@Spliter VARCHAR(5))
RETURNS @Tb TABLE(Keyword VARCHAR(45),Weight NUMERIC(2,1))
AS
BEGIN
DECLARE @Index int, @One VARCHAR(45), @Two 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 NUMERIC(2,1))INSERT INTO @TB(Keyword,Weight) VALUES(@One,@Two)
SET @String=RIGHT(@String,len(@String)-@Index)
SET @Index=Charindex(@Spliter,@String)
ENDRETURN
END 能不能帮我看看我写的这个哪里有问题啊, 为什么报错Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
好像不是这个转换的原因,我把所以的类型都改成VARCHAR了 还是一样的报错比如K1,W1,K2,W2,K3,W3,先分出K1,剩:W1,K2,W2,K3,W3,分出W2,剩K2,W2,K3,W3,然后写入表
再这样循环,思路应该没有错吧,主要是每个循环里面进行两次分割,没看出哪里错了啊。
功能说明:传入字符串跟分割符('''SGHE00000003'',''SGHE00000004'',''SGHE00000005'''),返回一个Table
*/
ALTER function [dbo].[fnSys_SplitString]
(
---字符串分割
@Str varchar(max), --传入的字符串
@SeprateStr varchar(10)--分隔符
)
Returns @temp table(Code varchar(100)) --返回一个Table
As
Begin
Declare @i int
Set @Str=REPLACE(@str,'''','')
Set @Str =rtrim(ltrim(@Str ))
Set @i=charindex(@SeprateStr,@Str )
While @i>=1
Begin
Insert @temp values(left(@Str ,@i-1))
Set @Str =substring(@Str ,@i+1,len(@Str )-@i)
Set @i=charindex(@SeprateStr,@Str )
End
If @Str <>''
Insert @temp values(@Str )
Return
End
(
@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
运行 SELECT * FROM [dbo].[StringSplit] ('K1,W1,K2,W2,K3,W3',',')报错
Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
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
SELECT * FROM [dbo].[StringSplit] ('K1,W1,K2,W2,K3,W3,',',')如果你W3后面加多一个,号就可以了.....是没i处理最后一个符号