比如存储过程中
CREATE procedure UP_CheckMsgArrived
@receiver varchar(200)
asdeclare @type int
declare @id int
declare @sender varchar(50)
if not exists(select * from dbo.MSG_Inbox where datediff(day,getdate(),MsgArrivedTime)=0) return -1
select top 1 @id=[ID],@type=MsgType,@sender=Sender from dbo.MSG_Inbox where datediff(day,getdate(),MsgArrivedTime)=0 order by MsgArrivedTime desc
if not exists(select * from UT_Tower where SIM=@sender)
begin
delete from dbo.MSG_Inbox where [ID]=@id
return -1
end
else
begin
if @type=0
begin
delete from dbo.MSG_Inbox where [ID]=@id
return 0
end
else
begin
if not exists(select * from dbo.MSG_Inbox where MsgType=@type and datediff(day,getdate(),MsgArrivedTime)=0 and MMSDownloadedTime is not null and MMSDownloadedState=100)
return -2
else
if @receiver=','
begin
delete from dbo.MSG_Inbox where [ID]=@id
end
else
begin
declare @MsgTitle varchar(200)
declare @mmsLoc varchar(500)
select @MsgTitle=MsgTitle,@mmsLoc=MMSContentLocation from dbo.MSG_Inbox where MsgType=1 and datediff(day,getdate(),MsgArrivedTime)=0 and MMSDownloadedTime is not null and MMSDownloadedState=100
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@receiver,1,@MsgTitle,@mmsLoc,getDate()) declare @address varchar(500)
select @address=Address from UT_Tower where SIM=@sender
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@receiver,0,@address,'',getDate())
delete from dbo.MSG_Inbox where [ID]=@id
end
end
end
return 0
GO
有个变量@receiver从存储过程外接受到的值为
@receiver='1,2,3,4,5'想要的结果:
1
2
3
4
5
得到结果之后,在存储过程中分别根据不同的@receiver分别插入到MSG_Inbox中,
就像上面的@recevier有五个值 ,就分5条insert语句分别插入到MSG_Inbox表中
该怎么写
我的数据库是用sql 2000的
CREATE procedure UP_CheckMsgArrived
@receiver varchar(200)
asdeclare @type int
declare @id int
declare @sender varchar(50)
if not exists(select * from dbo.MSG_Inbox where datediff(day,getdate(),MsgArrivedTime)=0) return -1
select top 1 @id=[ID],@type=MsgType,@sender=Sender from dbo.MSG_Inbox where datediff(day,getdate(),MsgArrivedTime)=0 order by MsgArrivedTime desc
if not exists(select * from UT_Tower where SIM=@sender)
begin
delete from dbo.MSG_Inbox where [ID]=@id
return -1
end
else
begin
if @type=0
begin
delete from dbo.MSG_Inbox where [ID]=@id
return 0
end
else
begin
if not exists(select * from dbo.MSG_Inbox where MsgType=@type and datediff(day,getdate(),MsgArrivedTime)=0 and MMSDownloadedTime is not null and MMSDownloadedState=100)
return -2
else
if @receiver=','
begin
delete from dbo.MSG_Inbox where [ID]=@id
end
else
begin
declare @MsgTitle varchar(200)
declare @mmsLoc varchar(500)
select @MsgTitle=MsgTitle,@mmsLoc=MMSContentLocation from dbo.MSG_Inbox where MsgType=1 and datediff(day,getdate(),MsgArrivedTime)=0 and MMSDownloadedTime is not null and MMSDownloadedState=100
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@receiver,1,@MsgTitle,@mmsLoc,getDate()) declare @address varchar(500)
select @address=Address from UT_Tower where SIM=@sender
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@receiver,0,@address,'',getDate())
delete from dbo.MSG_Inbox where [ID]=@id
end
end
end
return 0
GO
有个变量@receiver从存储过程外接受到的值为
@receiver='1,2,3,4,5'想要的结果:
1
2
3
4
5
得到结果之后,在存储过程中分别根据不同的@receiver分别插入到MSG_Inbox中,
就像上面的@recevier有五个值 ,就分5条insert语句分别插入到MSG_Inbox表中
该怎么写
我的数据库是用sql 2000的
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'SELECT a FROM dbo.fn_split(@str,',')
a
---------
1
2
3
4
5(5 row(s) affected)
我该怎么把个个分割出来的号码放到insert语句的value值的参数@recevier那里呢?
--调用
declare @str as varchar(30)
set @str = '15812405921,13737740287,15812405720'SELECT a FROM dbo.fn_split(@str,',')15812405921
13737740287
158124
declare @str as varchar(30)
调用的时候是不是直接在存储过程里面调用?遍历临时表的时候 是不是和在程序里面使用dateTable那样使用呀?
我这个人比较笨,也可能是心太急了,紧张过度了,什么思路也没有了。最好能写好贴一下。这个很重要,我怕我自己写的时候太紧张,出问题就麻烦了。明天要急用的。非常谢谢您好心人了
存储过程调用这个函数就好了。
有了这个临时表,就跟别的table一样使用就可以了。
--调用
declare @str as varchar(500)
set @str = '15812405921,13737740287,15812405720,15878359061,13926055674,13501502595,00000000000,11111111111,22222222222,33333333333' SELECT a FROM dbo.fn_split(@str,',') declare @MsgTitle varchar(200)
declare @mmsLoc varchar(500)
declare @address varchar(500) select @address=Address from UT_Tower where SIM=@sender
select @MsgTitle=MsgTitle,@mmsLoc=MMSContentLocation from dbo.MSG_Inbox where MsgType=1 and datediff(day,getdate(),MsgArrivedTime)=0 and MMSDownloadedTime is not null and MMSDownloadedState=100
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
while (dbo.fn_split.count>0)
begin
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(dbo.fn_split.a,1,@MsgTitle,@mmsLoc,getDate())
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(dbo.fn_split.a,0,@address,'',getDate())
end
delete from dbo.MSG_Inbox where [ID]=@id
end那你帮我看下我这样写该怎么改才对呢?
declare @str varchar(100)
declare @receiver varchar(20)
set @str = '15812405921,13737740287,15812405720'declare s cursor --declare 创建游标
static
for select a from dbo.fn_split(@str,',')
--定义变量open s --打开游标
fetch next from s into @receiver --提取上次提取行的下一行
while(@@fetch_status = 0)
begin
SELECT @receiver ------执行你的代码部分
fetch next from s INTO @receiver
END
close s --关闭游标
deallocate s --删除游标,释放资源--------------------
15812405921(1 row(s) affected)
--------------------
13737740287(1 row(s) affected)
--------------------
15812405720(1 row(s) affected)