Sql存储过程选择语句 [英] Sql stored procedure select statement

问题描述

我的另一个字段  my 数据库  table 名为FileName，我希望记录   文件名。我应该添加  代码
 
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[spx_Pager] 
  @ PageNo   int  =  1 ，
  @ ItemsPerPage   int  =  2 ，
  @ TotalRows   int  out，
  @ f_name   nva rchar （ 50 ）
  AS  
  BEGIN  
  SET   NOCOUNT   ON  
  DECLARE  
  @ StartIdx   int ，
  @ SQL   nvarchar （ max），
  @ SQL_Conditions   nvarchar （max），
  @ EndIdx   int  
 
  IF   @ PageNo <  1   SET   @ PageNo  =  1  
  IF   @ ItemsPerPage <  1   SET   @ ItemsPerPage  =  10  
 
  SET   @ StartIdx  =（ @ PageNo  -1）*  @ ItemsPerPage  +  1  
  SET   @ EndIdx  =（ @StartIdx  +  @ ItemsPerPage ） -   1  
  SET   @ f_name  =（ @ f_name ）
  SET   @ SQL  = '  SELECT FilePath 
 FROM（
 SELECT ROW_NUMBER（）O VER（ORDER BY ID）AS Row，* 
 FROM tblFiles）AS tbl WHERE Row> =' 
 +  CONVERT （  varchar （ 9 ）， @ StartIdx ） + '  AND 
 Row< =' +  CONVERT （ varchar （ 9 ）， @EndIdx ）
  EXEC  sp_executesql  @ SQL  
 
  SET   @ SQL  = '  SELECT @ TotalRows = COUNT（*）FROM tblFiles' 
  EXEC  sp_executesql 
  @ query  =  @ SQL ，
  @ params  = N '  @ TotalRows INT OUTPUT'，
  @ TotalRows  =  @ TotalRows  输出 
  END  
 
 我尝试过： 
 
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[spx_Pager] 
  @ PageNo   int  =  1 ，
  @ ItemsPerPage   int  =  2 ，
  @ TotalRows   int  out，
  @ f_name   nvarchar （ 50 ） 
  AS  
  BEGIN  
  SET   NOCOUNT   ON  
  DECLARE  
  @ StartIdx   int ，
  @ SQL   nvarchar （max），
  @ SQL_Conditions   nvarchar （max），
  @ EndIdx  < span class =code-keyword> int  
 
  IF   @ PageNo <  1   SET   @ PageNo  =  1  
  IF   @ ItemsPerPage <  1   SET   @ ItemsPerPage  =  10  
 
  SET   @ StartIdx  =（ @ PageNo  -1）*  @ ItemsPerPage  +  1  
  SET   @ EndIdx  =（ @StartIdx  +  @ ItemsPerPage ） -   1  
  SET   @ f_name  =（ @ f_name ）
  SET   @ SQL  = '  SELECT FilePath 
 FROM（
 SELECT ROW_NUMBER（）O VER（ORDER BY ID）AS Row，* 
 FROM tblFiles）AS tbl WHERE Row> =' 
 +  CONVERT （  varchar （ 9 ）， @ StartIdx ） + '  AND 
 Row< =' +  CONVERT （ varchar （ 9 ）， @EndIdx ）+ '  AND FileName = img1' 
  EXEC  sp_executesql  @ SQL  
 
  SET   @ SQL  = '  SELECT @ TotalRows = COUNT（* ）FROM tblFiles' 
  E. XEC  sp_executesql 
  @ query  =  @ SQL ，
  @ params  = N '  @ TotalRows INT OUTPUT' ，
  @ TotalRows  =  @ TotalRows   OUTPUT  
  END  

解决方案

  SET   @ SQL  = '  SELECT FilePath 
 FROM（SELECT ROW_NUMBER（）OVER（ORDER BY ID）AS Row，* 
 FROM tblFiles）AS tbl WHERE Row> =' 
 +  CONVERT （ varchar （ 9 ）， @ StartIdx ）+ '  AND 
 Row< =' +  CONVERT （ varchar （ 9 ）， @ EndIdx ）+ '  AND FileName = img1' 

有一个严重的问题，它所寻找的唯一文件将被命名为img1



你从哪里得到他的手术？剪切，粘贴，并要求我们为您修复它通常不能很好地工作。事实上， img1 甚至来自哪里，以便您选择它？它是表格栏吗？你选择但没有申报的具体价值？什么？搜索结果

I have another field in my database table called FileName, I want the record to be selected by FileName. What should I add to the code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_Pager]
	@PageNo int = 1,
	@ItemsPerPage int = 2,
	@TotalRows int out,
	@f_name nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE
    @StartIdx int,
    @SQL nvarchar(max),  
    @SQL_Conditions nvarchar(max),	 
    @EndIdx int
	
	IF @PageNo < 1 SET @PageNo = 1
	IF @ItemsPerPage < 1 SET @ItemsPerPage = 10

	SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
	SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
	SET @f_name = (@f_name)
	SET @SQL = 'SELECT FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl WHERE  Row >= ' 
						+ CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx)
	EXEC sp_executesql @SQL

	SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles' 
	EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

What I have tried:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_Pager]
	@PageNo int = 1,
	@ItemsPerPage int = 2,
	@TotalRows int out,
	@f_name nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE
    @StartIdx int,
    @SQL nvarchar(max),  
    @SQL_Conditions nvarchar(max),	 
    @EndIdx int
	
	IF @PageNo < 1 SET @PageNo = 1
	IF @ItemsPerPage < 1 SET @ItemsPerPage = 10

	SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
	SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
	SET @f_name = (@f_name)
	SET @SQL = 'SELECT FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl WHERE  Row >= ' 
						+ CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx) + ' AND FileName = img1'
	EXEC sp_executesql @SQL

	SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles' 
	EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

解决方案

SET @SQL = 'SELECT FilePath   
FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
FROM  tblFiles ) AS tbl WHERE  Row >= ' 
+ CONVERT(varchar(9), @StartIdx) + ' AND
Row <=  ' + CONVERT(varchar(9), @EndIdx) + ' AND FileName = img1'

Has a serious problem, the only file it will ever look for will be named "img1"

Where did you get his procedure? Cut, paste, and ask for us to fix it for you doesn't usually work well. Where, in fact, did img1 even come from in order for you to select for it? Is it a table column? Specific value you chose but didn't declare? What?

这篇关于Sql存储过程选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！