Sql stored procedure select statement

Question

0.00/5 (No votes)

See more:

SQL

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

Posted 12-Aug-16 0:53am

Jodelegend

Updated 12-Aug-16 1:54am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2016-08-12T01:54:00

SQL

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?