[Solved] SQL Server Stored Procedure Error: Invalid column name

makumbi
 
on Dec 07, 2022 04:35 AM
390 Views
Msg 207, Level 16, State 1, Line 9
Invalid column name 'S4'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'North'.

here is my table 
 
idNameContentTypeDataClassStream
1 How to use Font Awesome Icons in Asp.net.mp4 video/mp4      
2 How to use Font Awesome Icons in Asp.net.mp4 video/mp4   S4 North
PROCEDURE [dbo].[spx_Pager]

      @PageNo int = 1,

      @ItemsPerPage int = 2,

      @TotalRows int out,@c nvarchar(50),@s 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 @SQL = 'SELECT *

                FROM (

                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, *

                      FROM  tblFilesVideo ) AS tbl WHERE  Row >= '

                                    + CONVERT(varchar(9), @StartIdx) + ' AND

                       Row <=  ' + CONVERT(varchar(9), @EndIdx) +'and Class= '

                                    + CONVERT(varchar(50), @c) + ' AND

                       Stream=  ' + CONVERT(varchar(50), @s) 

      EXEC sp_executesql @SQL

 
      SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFilesVideo'

      EXEC sp_executesql

        @query = @SQL,

        @params = N'@TotalRows INT OUTPUT',

        @TotalRows = @TotalRows OUTPUT

END

 when i try to pass this here

exec spx_Pager '1','3','5','S4','North'

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 07, 2022 04:38 AM

Hi makumbi,

You need to add the single quote for Class and Stream column in the WHERE condition.

Alter your procedure with below.

ALTER PROCEDURE [dbo].[spx_Pager] 
	@PageNo int = 1, 
	@ItemsPerPage int = 2, 
	@TotalRows int out,
	@c nvarchar(50),
	@s 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 @SQL = 'SELECT * 
			FROM ( 
			SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
					FROM  tblFilesVideo ) AS tbl WHERE  Row >= ' 
					+ CONVERT(varchar(9), @StartIdx) + ' AND 
					Row <= ' + CONVERT(varchar(9), @EndIdx) +' AND Class = ''' 
								+ CONVERT(varchar(50), @c) + ''' AND 
					Stream = ''' + CONVERT(varchar(50), @s) + ''''
 
	EXEC sp_executesql @SQL
 
	SET @SQL = 'SELECT @TotalRows = COUNT(*) FROM tblFilesVideo'
 
	EXEC sp_executesql 
	@query = @SQL, 
	@params = N'@TotalRows INT OUTPUT', 
	@TotalRows = @TotalRows OUTPUT
END