就是说 要删除project表中的记录(ProjectID的形式是200904050101JDEE,2007090940404JSDN,200708080808KDEb),传入到存储过程只有ID 我要先处理这个ProjectID是否在其它的表中有记录,没有则删除不了提示,有则删除Project表中的记录以及和它相关的记录信息。请问有什么好的处理方法吗,(问题1 传入的ProjectID的拆分,问题2拆分后怎么分别去做业务的处理)(除了用游标)
调试欢乐多
这就存在了呗
(@origStr varchar(7000), --待拆分的字符串
@Str varchar(100)) --拆分标记,如','
RETURNS @splittable table
(
str_id varchar(4000) NOT NULL, --编号ID
string varchar(2000) NOT NULL --拆分后的字符串
)
AS
BEGIN
declare @strlen int,@postion int,@start int,@sublen int,
@TEMPstr varchar(200),@TEMPid int
SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,
@TEMPstr='',@TEMPid=0
if(RIGHT(@origStr,1)<>@Str )
begin
set @origStr = @origStr + @Str
end
WHILE((@postion<=@strlen) and (@postion !=0))
BEGIN
IF(CHARINDEX(@Str,@origStr,@postion)!=0)
BEGIN
SET @sublen=CHARINDEX(@Str,@origStr,@postion)-@postion;
END
ELSE
BEGIN
SET @sublen=@strlen-@postion+1;
END
IF(@postion<=@strlen)
BEGIN
SET @TEMPid=@TEMPid+1;
SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);
INSERT INTO @splittable(str_id,string)
values(@TEMPid,@TEMPstr)
IF(CHARINDEX(@Str,@origStr,@postion)!=0)
BEGIN
SET @postion=CHARINDEX(@Str,@origStr,@postion)+1
END
ELSE
BEGIN
SET @postion=@postion+1
END
END
END
RETURN
END例如:select * from uf_StrSplit('1,1,2,50',',')输出结果:
str_id string
1 1
2 1
3 2
4 50 拆分后,容易写了吧.不多说了
这样?
create proc del_P1(@IDs nvarchar(2000))
as
declare @Error nvarchar(200)
begin tran
if not exists(select 1 from OtherTable where ','+@IDs+',' like '%'+projectID+'%')
begin
set @Error=N'在其它表不存在,删除不了'
goto Error
enddelete OtherTable where ','+@IDs+',' like '%'+projectID+'%'
delete project where ','+@IDs+',' like '%'+projectID+'%'commit tran
returnError:
raiserror 50000 @Error
rollback tran
if not object_id('products') is null
drop table products
go
create table products(
productId varchar(16),
[name] varchar(100)
)
go
insert into products select
'200904050101JDEE', 'AAA' union all select
'200709094040JSDN', 'BBB' union all select
'200708080808KDEb', 'CCC'
go
--用动态T-SQL的方法:
create proc del_product(@Ids varchar(1000))
as
declare @Error varchar(200)
declare @sql nvarchar(1000)
declare @count int
begin tran
set @Ids = replace(@Ids, ',', ''',''')
set @sql = N'select @count = count(1) from products where productId in (''' + @Ids + ''')'
exec sp_executesql @sql, N'@count int output', @count output
if @count <= 0
begin
set @Error = '在其它表不存在,删除不了'
goto Error
end
set @sql = N'delete from products where productId in (''' + @Ids + ''')'
exec(@sql)
commit tran
return
Error:
raiserror 50000 @Error
rollback tran--exec del_product '200904050101JDEE,200709094040JSDN,200708080808KDEb'
加两个逗号就可以了
方法二: '%,'+ProjectID+',%' like ','+ProjectIDList+','
where ','+@ProjectIDList+',' like '%,'+cast(ProjectID as varchar(12))+',%'