create procedure C_DELETE_COMMENT
(@CommentID varchar(100))
AS
EXEC ('delete FROM sysComment WHERE CommentID IN (' + @CommentID + ')')
GO
需要使用Substring(0,temcommentid.Length-1);消除字符串最后的一个逗号
字符串处理
1.存储过程实现
create
procedure
sp_split_string
(
@string NVARCHAR ( 4000 )
)
AS
BEGIN
DECLARE @object_id nvarchar ( 500 )
DECLARE @i INT
DECLARE @len INT
print @string
IF ( @string IS NULL ) OR ( LTRIM ( @string ) = '' )
RETURN
WHILE CHARINDEX ( ' , ' , @string ) > 0
BEGIN
SET @len = LEN ( @string )
SET @i = CHARINDEX ( ' , ' , @string )
SET @object_id = LEFT ( @string , @i - 1 )
INSERT INTO a (id) VALUES ( @object_id ) -- 少做修改,改成需要的sql语句即可
SET @string = RIGHT ( @string , @len - @i )
END
SET @object_id = @string
INSERT INTO a (id) VALUES ( @object_id ) -- 少做修改,改成需要的sql语句即可
END
go
-- 测试
-- select * from a
-- exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
-- select * from a
(
@string NVARCHAR ( 4000 )
)
AS
BEGIN
DECLARE @object_id nvarchar ( 500 )
DECLARE @i INT
DECLARE @len INT
print @string
IF ( @string IS NULL ) OR ( LTRIM ( @string ) = '' )
RETURN
WHILE CHARINDEX ( ' , ' , @string ) > 0
BEGIN
SET @len = LEN ( @string )
SET @i = CHARINDEX ( ' , ' , @string )
SET @object_id = LEFT ( @string , @i - 1 )
INSERT INTO a (id) VALUES ( @object_id ) -- 少做修改,改成需要的sql语句即可
SET @string = RIGHT ( @string , @len - @i )
END
SET @object_id = @string
INSERT INTO a (id) VALUES ( @object_id ) -- 少做修改,改成需要的sql语句即可
END
go
-- 测试
-- select * from a
-- exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
-- select * from a
2.一个用于处理分隔","的函数tf_split_str
--
1. 建立测试数据
-- 1.1 create table TypeAdv
CREATE TABLE TypeAdv
(
id int ,
TypeName varchar ( 200 )
)
-- 1.2 insert data
insert into TypeAdv(id,TypeName) values ( 1 , ' 电脑网络 ' )
insert into TypeAdv(id,TypeName) values ( 2 , ' 游戏动漫 ' )
insert into TypeAdv(id,TypeName) values ( 3 , ' 软件下载 ' )
insert into TypeAdv(id,TypeName) values ( 4 , ' 商务商铺 ' )
insert into TypeAdv(id,TypeName) values ( 5 , ' 建站服务 ' )
insert into TypeAdv(id,TypeName) values ( 6 , ' 门户综合 ' )
insert into TypeAdv(id,TypeName) values ( 7 , ' 影视音乐 ' )
insert into TypeAdv(id,TypeName) values ( 8 , ' 休闲娱乐 ' )
insert into TypeAdv(id,TypeName) values ( 9 , ' 生活资讯 ' )
insert into TypeAdv(id,TypeName) values ( 10 , ' 文学小说 ' )
-- 1.3 create table TypeAdv
CREATE TABLE GroupAdv
(
id int ,
GroupName varchar ( 200 ),
IntentionSet varchar ( 200 )
)
-- 1.4 insert data
insert into GroupAdv(id,GroupName,IntentionSet) values ( 5 , ' 广告分组一 ' , ' 1,2,3,4,5,6,7,10 ' )
insert into GroupAdv(id,GroupName,IntentionSet) values ( 6 , ' 广告分组二 ' , ' 1,2,3,4,5,6 ' )
-- 2. 创建分隔“,”的函数
CREATE FUNCTION tf_split_str
(
@string NVARCHAR ( 4000 )
)
RETURNS varchar ( 4000 )
AS
BEGIN
DECLARE @object_id nvarchar ( 400 )
DECLARE @i INT
DECLARE @len INT
declare @return varchar ( 400 )
set @return = ''
IF ( @string IS NULL ) OR ( LTRIM ( @string ) = '' )
RETURN @return
WHILE CHARINDEX ( ' , ' , @string ) > 0
BEGIN
SET @len = LEN ( @string )
SET @i = CHARINDEX ( ' , ' , @string )
SET @object_id = LEFT ( @string , @i - 1 )
select @return = @return + ' , ' + TypeName from TypeAdv where id = @object_id
SET @string = RIGHT ( @string , @len - @i )
END
SET @object_id = @string
select @return = @return + ' , ' + TypeName from TypeAdv where id = @object_id
set @return = stuff ( @return , 1 , 1 , '' )
RETURN @return
END
-- 3.测试
select b.id,b.GroupName,dbo.tf_split_str(b.IntentionSet) from GroupAdv b
-- 1.1 create table TypeAdv
CREATE TABLE TypeAdv
(
id int ,
TypeName varchar ( 200 )
)
-- 1.2 insert data
insert into TypeAdv(id,TypeName) values ( 1 , ' 电脑网络 ' )
insert into TypeAdv(id,TypeName) values ( 2 , ' 游戏动漫 ' )
insert into TypeAdv(id,TypeName) values ( 3 , ' 软件下载 ' )
insert into TypeAdv(id,TypeName) values ( 4 , ' 商务商铺 ' )
insert into TypeAdv(id,TypeName) values ( 5 , ' 建站服务 ' )
insert into TypeAdv(id,TypeName) values ( 6 , ' 门户综合 ' )
insert into TypeAdv(id,TypeName) values ( 7 , ' 影视音乐 ' )
insert into TypeAdv(id,TypeName) values ( 8 , ' 休闲娱乐 ' )
insert into TypeAdv(id,TypeName) values ( 9 , ' 生活资讯 ' )
insert into TypeAdv(id,TypeName) values ( 10 , ' 文学小说 ' )
-- 1.3 create table TypeAdv
CREATE TABLE GroupAdv
(
id int ,
GroupName varchar ( 200 ),
IntentionSet varchar ( 200 )
)
-- 1.4 insert data
insert into GroupAdv(id,GroupName,IntentionSet) values ( 5 , ' 广告分组一 ' , ' 1,2,3,4,5,6,7,10 ' )
insert into GroupAdv(id,GroupName,IntentionSet) values ( 6 , ' 广告分组二 ' , ' 1,2,3,4,5,6 ' )
-- 2. 创建分隔“,”的函数
CREATE FUNCTION tf_split_str
(
@string NVARCHAR ( 4000 )
)
RETURNS varchar ( 4000 )
AS
BEGIN
DECLARE @object_id nvarchar ( 400 )
DECLARE @i INT
DECLARE @len INT
declare @return varchar ( 400 )
set @return = ''
IF ( @string IS NULL ) OR ( LTRIM ( @string ) = '' )
RETURN @return
WHILE CHARINDEX ( ' , ' , @string ) > 0
BEGIN
SET @len = LEN ( @string )
SET @i = CHARINDEX ( ' , ' , @string )
SET @object_id = LEFT ( @string , @i - 1 )
select @return = @return + ' , ' + TypeName from TypeAdv where id = @object_id
SET @string = RIGHT ( @string , @len - @i )
END
SET @object_id = @string
select @return = @return + ' , ' + TypeName from TypeAdv where id = @object_id
set @return = stuff ( @return , 1 , 1 , '' )
RETURN @return
END
-- 3.测试
select b.id,b.GroupName,dbo.tf_split_str(b.IntentionSet) from GroupAdv b