存储过程在服务器中不起作用

问题描述：

这是我的存储过程，该存储过程在本地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

存储过程在服务器中不起作用

相关推荐