商店程序不归还记录？ [英] store procedure not return record ?

问题描述

我运行此sp它不会返回值。

i am run this sp it will not return value.

alter PROCEDURE [USP_GetListOfQuestion]
	-- Add the parameters for the stored procedure here
	@TestPaperCode varchar
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @RowCount INT;
Select @RowCount = Count(*) from tbl_TestPaper where TestPaperCode = @TestPaperCode

Create table #Temp
(
QuestionId bigint
)


DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=@RowCount)
BEGIN
--Change
DECLARE @NoOfQst INT;

select @NoOfQst =  max(NoOfQuestions ) from (SELECT Row_Number() OVER
 (ORDER
 BY
  TestPaperId) RoNo,* from tbl_TestPaper where TestPaperCode = @TestPaperCode) X
  where RoNo=@intFlag


Insert Into #Temp(QuestionId)
Select Questionid from (
Select top(@NoOfQst) Questionid as 'Questionid' from dbo.tbl_Questions a
inner join 
(SELECT Row_Number() OVER
 (ORDER
 BY
  TestPaperId) RoNo,* from tbl_TestPaper where TestPaperCode = @TestPaperCode) b 
on a.CategoryId=b.CategoryId and a.Marks=b.Marks

Where RoNo=@intFlag
ORDER BY NEWID())Y
--Change
print @intFlag
SET @intFlag = @intFlag + 1
END
    -- Insert statements for procedure here
Select * from #Temp

drop table #Temp

END
GO

请帮我，我正在使用NewID方法。

please help me i am using NewID method.

推荐答案

#temp为空是有各种各样的原因。一个开始就是知道是否有任何插入发生。



我打破了它并运行了一些它，看看它们返回的地方找不到你想要的东西。或者添加一些日志代码，以便它告诉你它做了什么和不做什么。

There's all sorts of reasons for #temp to be empty. A start would be to know if there's any inserts happening.

I'd break this down and run bits of it to see what they return to work out where it's not doing what you want. OR add some logging code so it tells you what it did and did not do.

在这里看到不要使用drop table #Temp，因为你是从Temp表中选择记录。 $>


在MySQL中，当数据库连接关闭时，会自动删除临时表。如果您计划在存储过程后打开连接，则临时表将存在于磁盘上，直到该连接关闭为止。性能影响取决于许多因素，例如您在服务器上配置临时表存储的方式，表中的数据量等等。

See in this don't use drop table #Temp because you are selecting record from Temp table.

In MySQL, temporary tables are dropped automatically when a database connection is closed. If you plan on leaving your connection open after the stored procedure, your temp table will exist on disk until that connection is closed. The performance implications depend on many factors, such as how you have configured temporary table storage on your server, how much data is in the table, etc.

这篇关于商店程序不归还记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！