CREATE TABLE emp(eid INT IDENTITY(1,1),eName nVARCHAR(50)) CREATE TABLE dep(did INT IDENTITY(1,1),dName nVARCHAR(50)) CREATE TABLE erd(did INT,eid INT) GO INSERT dep SELECT N'X' UNION ALL SELECT N'Y' UNION ALL SELECT N'Z' INSERT emp SELECT N'a' UNION ALL SELECT N'b' UNION ALL SELECT N'c' UNION ALL SELECT N'd' UNION ALL SELECT N'e'GO SELECT * FROM emp SELECT * FROM dep SELECT * FROM erd GO CREATE PROC p --注意这里我的oldid,newid的顺序与你的是反的,看数据时注意一下就可以了。 @newid INT,@oldid INT,@ids VARCHAR(1000) AS BEGIN DELETE a FROM erd a INNER JOIN (SELECT id FROM (SELECT x = CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)) a CROSS APPLY (SELECT id=t.x.value('.','int') FROM a.x.nodes('//r') AS t(x)) b ) b ON a.eid = b.id AND a.did = @oldid INSERT erd SELECT @newid,b.id FROM (SELECT x = CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)) a CROSS APPLY (SELECT id=t.x.value('.','int') FROM a.x.nodes('//r') AS t(x)) b WHERE NOT EXISTS(SELECT 1 FROM erd WHERE eid=b.id) AND @newid!=0 END GO EXEC p 1,0,'1,2,3,4,5' SELECT * FROM erd /* 1 1 1 2 1 3 1 4 1 5 */EXEC p 0,1,'1,2,3' SELECT * FROM erd /* 1 4 1 5 */EXEC p 2,1,'4,5' SELECT * FROM erd /* 2 4 2 5*/EXEC p 2,0,'1,3,4' SELECT * FROM erd /* 2 4 2 5 2 1 2 3 */ GO DROP TABLE emp,dep,erd GO DROP PROC p GO
GO INSERT dep SELECT N'X' UNION ALL SELECT N'Y' UNION ALL SELECT N'Z' INSERT emp SELECT N'a' UNION ALL SELECT N'b' UNION ALL SELECT N'c' UNION ALL SELECT N'd' UNION ALL SELECT N'e'这两句什么意思?
CREATE TABLE dep(did INT IDENTITY(1,1),dName nVARCHAR(50))
CREATE TABLE erd(did INT,eid INT)
GO
INSERT dep SELECT N'X' UNION ALL SELECT N'Y' UNION ALL SELECT N'Z'
INSERT emp SELECT N'a' UNION ALL SELECT N'b' UNION ALL SELECT N'c' UNION ALL SELECT N'd' UNION ALL SELECT N'e'GO
SELECT * FROM emp
SELECT * FROM dep
SELECT * FROM erd
GO
CREATE PROC p
--注意这里我的oldid,newid的顺序与你的是反的,看数据时注意一下就可以了。
@newid INT,@oldid INT,@ids VARCHAR(1000)
AS
BEGIN
DELETE a FROM erd a
INNER JOIN
(SELECT id FROM
(SELECT x = CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)) a
CROSS APPLY
(SELECT id=t.x.value('.','int') FROM a.x.nodes('//r') AS t(x)) b
) b
ON a.eid = b.id AND a.did = @oldid INSERT erd SELECT @newid,b.id
FROM
(SELECT x = CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)) a
CROSS APPLY
(SELECT id=t.x.value('.','int') FROM a.x.nodes('//r') AS t(x)) b
WHERE NOT EXISTS(SELECT 1 FROM erd WHERE eid=b.id) AND @newid!=0
END
GO
EXEC p 1,0,'1,2,3,4,5'
SELECT * FROM erd
/*
1 1
1 2
1 3
1 4
1 5
*/EXEC p 0,1,'1,2,3'
SELECT * FROM erd
/*
1 4
1 5
*/EXEC p 2,1,'4,5'
SELECT * FROM erd
/*
2 4
2 5*/EXEC p 2,0,'1,3,4'
SELECT * FROM erd
/*
2 4
2 5
2 1
2 3
*/
GO
DROP TABLE emp,dep,erd
GO
DROP PROC p
GO
INSERT dep SELECT N'X' UNION ALL SELECT N'Y' UNION ALL SELECT N'Z'
INSERT emp SELECT N'a' UNION ALL SELECT N'b' UNION ALL SELECT N'c' UNION ALL SELECT N'd' UNION ALL SELECT N'e'这两句什么意思?
emp为员工,插入a,b,c,d,e五员工。N表示字串以unicode编码。
你好,fcuandy,还的麻烦你一下,上次指点的存储过程
http://topic.csdn.net/u/20081106/12/1807bb8f-9b87-4425-b231-f8b13560aa70.html
我现在还有点问题,我希望在原来的一个存储过程里调用这个存储过程,
create PROC [dbo].[AppendUser](
@groupId INT=null,
@name nvarchar(80)
)
AS
DECLARE @uid int
INSERT Info (groupId,单位名称)Values(@groupId,@name)
SET @uid = SCOPE_IDENTITY() -----添加映射关系 declare @mm varchar(2)
set @mm = Convert(varchar(2),@uid)
Exec [dbo].[CreateIDRelation]
@adminid , 0, @mm 执行出现错误 :DELETE 失败,因为下列 SET 选项的设置不正确:'ARITHABORT'。请确保 SET 选项可正确用于计算列和/或查询通知和/或 xml 数据类型方法的索引视图和/或索引
请指教
ex3206 2008年11月07日 13点24分37秒 说:
@adminid 写错了,应该是 @groupId