Sql存储过程选择语句

本文介绍了Sql存储过程选择语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我的另一个字段 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 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 codeSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spx_Pager]@PageNo int = 1,@ItemsPerPage int = 2,@TotalRows int out,@f_name nvarchar(50)ASBEGIN SET NOCOUNT ON DECLARE @StartIdx int, @SQL nvarchar(max), @SQL_Conditions nvarchar(max), @EndIdx intIF @PageNo < 1 SET @PageNo = 1IF @ItemsPerPage < 1 SET @ItemsPerPage = 10SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1SET @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 @SQLSET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles'EXEC sp_executesql @query = @SQL, @params = N'@TotalRows INT OUTPUT', @TotalRows = @TotalRows OUTPUTENDWhat I have tried:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spx_Pager]@PageNo int = 1,@ItemsPerPage int = 2,@TotalRows int out,@f_name nvarchar(50)ASBEGIN SET NOCOUNT ON DECLARE @StartIdx int, @SQL nvarchar(max), @SQL_Conditions nvarchar(max), @EndIdx intIF @PageNo < 1 SET @PageNo = 1IF @ItemsPerPage < 1 SET @ItemsPerPage = 10SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1SET @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 @SQLSET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles'EXEC sp_executesql @query = @SQL, @params = N'@TotalRows INT OUTPUT', @TotalRows = @TotalRows OUTPUTEND 解决方案 SET @SQL = 'SELECT FilePathFROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Row, *FROM tblFiles ) AS tbl WHERE Row >= '+ CONVERT(varchar(9), @StartIdx) + ' ANDRow <= ' + 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存储过程选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！