存储过程中的逻辑思想问题

Question

这是我建的一个存储过程，以及执行该存过后的结果

这是在存过中的视图V_vister

怎么我在执行存过的时候会没有结果呢？请问是哪出错了？…

Answer 1

You didn't return anything from sproc.

Answer 2

我在每个判断处都有个查询表记录，不管怎么样，都应该会返回数据的啊，怎么会没数据呢？

Answer 3

Need output parameter, take look at sample E under 'CREATE PROCEDURE (Transact-SQL)' in books online.

Answer 4

呃，不太明白，那我该怎么做？或是怎么修改这个存过才能达到我想要的结果…

Answer 5

Add output parameter in the sp. Did you look at sample in books online yet by the way?

Answer 6

我把网址贴出来，里面就有关于output 的例子，去看看例子就知道怎么写了。

link: http://msdn.microsoft.com/en-us/library/ms187926.aspx 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 7

由于我学SQL2005没多久，有些关键字实在是不明白,我看那个OUTPUT关键字好像只能输出一列，但我想要的是查询出符合我逻辑条件的那个表，我写的那个存过我不明白到底是哪出问题了，有时用户1访问用户2，会先执行insert语句在根据条件查询出那个表，但反过来用户2访问用户1却什么也没有，就是我上面的那个截图，恕我愚昧，实在不明白，能将清楚点吗？谢谢…

Answer 8

版主，帮我解决一下啊，谢谢了啊，这个存过我该怎么修改下？？？…

Answer 9

这是我测试的代码，各个功能都可以运行。

--create table VInfo (VID INT, UID INT, byUID INT,Vtime date)
--create table Uinfos ( UID INT,Uname varchar(10), Upwd varchar(10))
--insert into VInfo values (1,1,1,'2011-10-10')
--insert into VInfo  values (1,2,1,'2011-10-10')
--insert into VInfo  values (6,3,1,'2011-10-10')
--insert into VInfo values  (3,4,1,'2011-10-10')
--insert into Uinfos values ( 1,'name1','password1')
--insert into Uinfos values ( 2,'name2','password2')
--insert into Uinfos values (3,'name3','password3')
----insert into Uinfos values ( 4,'name4','password4')
--insert into Uinfos values ( 7,'name7','password5')
--insert into Uinfos values ( 9,'name9','password9')

--create view v_vister 
--as 
--select VInfo.VID,VInfo.UID, VInfo.byUID, VInfo.Vtime, Uinfos.Uname 
--from VInfo inner join Uinfos  on VInfo.UID = Uinfos.UID

--select * from v_vister
--alter proc p_vister
--(@uid int, @byuid int)
--as 
--begin
--if (@uid = @byuid or @uid = 5)
--	begin 
--	select top 10 * from v_vister where byuid = @byuid order by vtime desc
--	end 
--else 
--begin 
--if(exists(select * from VInfo where uid = @uid))
--begin
--update VInfo set Vtime = GETDATE() where uid = @uid
--select top 10 * from V_vister where byuid = @byuid order by vtime desc
--end
--else
--begin 
--insert into VInfo(uid, byuid ) values (@uid, @byuid)
--select top 10 * from V_vister where byuid = @byuid order by vtime desc 
--end 
--end 
--end
exec p_vister 1,1
--exec p_vister 5,1
--exec P_vister 6,1
--exec p_vister 9,1

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 10

那版主有没有把两个用户反过来试过呢？例如exec p_vister 1,9      exec P_vister 1,6   就像我上面的那个截图为什么会没有任何数据呢，按照我的这个逻辑  如果是exec P_vister 1,6  那么应该执行的代码是这段：

if(exists(select * from VInfo where uid = @uid))
begin
update VInfo set Vtime = GETDATE() where uid = @uid
select top 10 * from V_vister where byuid = @byuid order by vtime desc
end
else
begin
insert into VInfo(uid, byuid ) values (@uid, @byuid)
select top 10 * from V_vister where byuid = @byuid order by vtime desc
end
end

但不管怎样最少应该会返回一条数据的啊，怎么会什么都没有呢？…

 

Answer 11

这是我执行存过的一个结果

这是什么意思？…

Answer 12

那版主有没有把两个用户反过来试过呢？例如exec p_vister 1,9      exec P_vister 1,6   就像我上面的那个截图为什么会没有任何数据呢，按照我的这个逻辑  如果是exec P_vister 1,6  那么应该执行的代码是这段：

if(exists(select * from VInfo where uid = @uid))
begin
update VInfo set Vtime = GETDATE() where uid = @uid
select top 10 * from V_vister where byuid = @byuid order by vtime desc
end
else
begin
insert into VInfo(uid, byuid ) values (@uid, @byuid)
select top 10 * from V_vister where byuid = @byuid order by vtime desc
end
end

但不管怎样最少应该会返回一条数据的啊，怎么会什么都没有呢？…

 

你好，如果你用的是我的上面给的数据的话，这条语句 select top 10 * from V_vister where byuid = @byuid order by vtime desc 是没有结果的。因为在我的数据中没有byuid 没有等于6 的情况。你可以在这个语句之前加上select top 10 * from V_vister，V_vister 中的数据是有变化的。

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 13

这是我执行存过的一个结果

这是什么意思？…

请问你是想完成这样的功能吗？

@return_status:  is an optional integer variable that stores the return status of a stored procedure.@return_status must be declared in the batch or stored procedure before it is used in anexecute statement.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 14

感谢版主，我今天终于找出问题所在了，这个问题OK了…

Answer 15

你好，

如果问题已经解决了，请标记正确的答案或是将你的答案分享给我们。这会就其他的成员有很大的帮助。

谢谢。

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.