从第二次执行一个存储过程开始，没有返回结果。
这是一个简单的存储过程：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[test]
as
begin
declare @user_behavior TABLE
(
user_id int
)
declare @user_id int
declare user_ids cursor for select user_id from a_user
open user_ids
while(@@fetch_status=0)
begin
insert into @user_behavior values(@user_id);
fetch next from user_ids into @user_id
end
CLOSE user_ids
Deallocate user_ids
select * from @user_behavior;
end
执行：
USE [CRM_ADMIN]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[test]
SELECT'Return Value' = @return_value
GO
第一次执行结果：
--------------------
user_id |
---------------
2 |
以后再执行就没查询到数据：
--------------------
user_id |
---------------
|
这是为什么？？？
存储过程 sqlserver
[解决办法]
create procedure [dbo].[test]
as
begin
declare @user_behavior TABLE
(
user_id int
)
declare @user_id int
declare user_ids cursor for select user_id from a_user
open user_ids
fetch next from user_ids into @user_id -->你缺少了这一句
while(@@fetch_status=0)
begin
insert into @user_behavior values(@user_id);
fetch next from user_ids into @user_id
end
CLOSE user_ids
Deallocate user_ids
select * from @user_behavior;
end
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[test]
SELECT 'Return Value' = @return_value
GO
