Image gallery by category

That's the code part where you are passing value but you also need to declare it as I did

USE [vo]
GO
/****** Object:  StoredProcedure [dbo].[spx_Pager]    Script Date: 01/26/2013 01:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[spx_Pager]
 
      @PageNo int = 1,
 
      @ItemsPerPage int = 10,
      @cat_id 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 pd_picname
 
                FROM (
 
                SELECT  ROW_NUMBER() OVER(ORDER BY pd_id) AS Row, *
 
                      FROM  tbl_product ) AS tbl WHERE cat_id=@cat_id  Row >= '
 
                                    + CONVERT(varchar(9), @StartIdx) + ' AND
 
                       Row <=  ' + CONVERT(varchar(9), @EndIdx)
 
      EXEC sp_executesql @SQL
 
  
 
      SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tbl_product'
 
      EXEC sp_executesql
 
        @query = @SQL,
 
        @params = N'@TotalRows INT OUTPUT',
 
        @TotalRows = @TotalRows OUTPUT
 
END

Hi,

I searched a lot and ultimately success

this two links helped me.

http://www.codeproject.com/Questions/398571/Must-declare-the-scalar-variable-cid

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ab9bf39e-db2f-42ac-b1ac-1edd740c60c4

this is compelet sp

alter PROCEDURE [dbo].[spx_Pager]

      @PageNo int = 1,

      @ItemsPerPage int = 2,

      @TotalRows int out,
      @cat_id int
     

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 pd_picname

                FROM (

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

                      FROM  tbl_product ) AS tbl WHERE  Row >= ' 

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

                       Row <=  ' + CONVERT(varchar(9), @EndIdx) + ' And cat_id='+CAST( @cat_id AS varchar)

      EXEC sp_executesql @SQL

 

      SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tbl_product where cat_id='+CAST( @cat_id AS varchar)

      EXEC sp_executesql

        @query = @SQL,

        @params = N'@TotalRows INT OUTPUT',

        @TotalRows = @TotalRows OUTPUT

END