@thisID Nvarchar(255), --ID为一组数据,都是,号格开的,如:"1,2,3,4"
@thisName Nvarchar(255),
@thisDate DateTime,
@thisResult int OUTPUT我想通过@thisID读取table1表里的数据temp1
然后通过@thisName读取table2表里的数据temp2
然后判断如果temp1=1 and temp2>=1则执行一个插入操作,插入成功返回@thisResult=1,失败@thisResult=0急用,望高手帮帮小弟了
@thisName Nvarchar(255),
@thisDate DateTime,
@thisResult int OUTPUT我想通过@thisID读取table1表里的数据temp1
然后通过@thisName读取table2表里的数据temp2
然后判断如果temp1=1 and temp2>=1则执行一个插入操作,插入成功返回@thisResult=1,失败@thisResult=0急用,望高手帮帮小弟了
(
@thisID Nvarchar(255),
@thisName Nvarchar(255),
@thisDate DateTime,
@thisResult int OUTPUT
)
AS
BEGIN
--第一个要求
SELECT * FROM table1 WHERE CHARINDEX(',' + RTRIM(ID) + ',' , ','+@thisID+',')>0
--第二个要求,不明你的@thisName可以用作什么??表名吗?
EXEC ('SELECT * FROM ' + @thisName)
--这个thisDate干什么写不下去了,不明白你的意思。楼主可否给点示例数据,和你希望得到的结果数据?
ID TEMP1
1 0
2 1
3 0
4 1
表2
ID TEMP2
abc 5
hhh 1
test 0
got 7
表3
ID NAME UP_TIME
插入成功则直接返回一个@thisResult=1多谢大家 了
是不是说在选出的数据中,如果存在temp1=1并且temp2>=1? 然后进行下一步的操作?
--是的
if not exists(select 1 from dbo.sysobjects where name ='表1')
begin
create table 表1(ID int,TEMP1 int)
insert into 表1 select 1, 0
insert into 表1 select 2, 1
insert into 表1 select 3, 0
insert into 表1 select 4, 1
end
if not exists(select 1 from dbo.sysobjects where name ='表2')
begin
create table 表2(ID varchar(100),TEMP2 int)
insert into 表2 select 'abc', 0
insert into 表2 select 'hhh', 1
insert into 表2 select 'test', 0
insert into 表2 select 'got', 1
end
if not exists(select 1 from dbo.sysobjects where name ='表3')
create table 表3(ID int,NAME varchar(20), UP_TIME datetime)if exists(select * from dbo.sysobjects where name='MP' and xtype=N'P')
drop proc dbo.MP
GO
create proc MP
(
@thisID Nvarchar(255), --ID为一组数据,都是,号格开的,如:"1,2,3,4"
@thisName Nvarchar(255),
@thisDate DateTime,
@thisResult int OUTPUT
) as
declare @i int,@id int
set @thisID=@thisID+','
while len(@thisID)>len(replace(@thisID,',',''))
begin
select @i=charindex(',',@thisID)
select @id=cast(substring(@thisID,1,@i-1) as int),
@thisID=stuff(@thisID,1,@i,'')
if exists(select * from [表1] where ID=@ID and temp1=1)
and exists(select * from [表2] where ID=@thisName and temp2>=1)
insert into [表3] SELECT @ID,@thisName,@thisDate
if @@error=0 and @@rowcount>0
set @thisResult=1
else
set @thisResult=0
endGO
--调用exec MP '2,12,13','got','2006-6-19',1
select * from 表3
(所影响的行数为 1 行)
(所影响的行数为 1 行)ID NAME UP_TIME
----------- ---------- ------------------------------------------------------
2 got 2006-06-19 00:00:00.000(所影响的行数为 1 行)(所影响的行数为 1 行)
AS
BEGIN
IF (SELECT COUNT(*) FROM table1 WHERE CHARINDEX(',' + RTRIM(ID) + ',' , ','+@thisID+',')>0 AND temp1=1)>0
AND (SELECT COUNT(*) FROM table2 WHERE ID=@thisname AND Temp2>=1)>0
BEGIN
INSERT table3 .. -- 不知道你表3里面的ID,Name分别插什么值,没有说明
if @@error=0 and @@rowcount>0
set @thisResult=1
else
set @thisResult=0
END
END