存储过程在服务器中不起作用 [英] stored procedure is not working in server

问题描述

这是我的存储过程，该存储过程在本地SQL服务器中运行正常，但在Web中不运行

Here is my stored procedure which is working perfectly in local SQL server but not working in web

create PROCEDURE [dbo].[searchresult]
      @PageIndex INT
      ,@PageSize INT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [id] ASC
      )AS RowNumber
      ,[id]
      ,[title]
      ,[type]
      ,[cost]
      ,[date]
     INTO Results
      FROM [test]
      SELECT * FROM Results
      WHERE RowNumber BETWEEN @PageIndex and (@PageSize+@PageIndex)-1
      DROP TABLE Results
END

第一次执行上述步骤时，显示

For the first time when this above procedure executes it is showing

Invalid object name ''Results''

并在下次执行时显示

and for the next time of execution it is displaying

There is already an object named ''Results'' in the database.

推荐答案

尝试将数据存储在临时表中，如下所示

Try storing your data in a temp table as shown below

create PROCEDURE [dbo].[searchresult]
      @PageIndex INT
      ,@PageSize INT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [id] ASC
      )AS RowNumber
      ,[id]
      ,[title]
      ,[type]
      ,[cost]
      ,[date]
     INTO #Results
      FROM [test]
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN @PageIndex and (@PageSize+@PageIndex)-1
      DROP TABLE #Results
END

这篇关于存储过程在服务器中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！