I Am getting error as Must declare the scalar variable "@Imageid" for the following code,while executing,
exec spGetPager ' ' , ' ', ' 3 ', ' '
In my coding I pass querystring value like 1,2,3 etc
CREATE PROCEDURE spGetPager
@PageNo int = 1,
@ItemsPerPage int = 1,
@Imageid int,
@TotalRows int out
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 Imageid,Imagepath,imagename,username
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY Imageid) AS Row, *
FROM Imageupload ) AS tbl WHERE Imageid=@Imageid AND Row >= '
+ CONVERT(varchar(9), @StartIdx) + ' AND
Row <= ' + CONVERT(varchar(9), @EndIdx)
EXEC sp_executesql @SQL
SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM Imageupload'
EXEC sp_executesql
@query = @SQL,
@params = N'@TotalRows INT OUTPUT',
@TotalRows = @TotalRows OUTPUT
END