CODEWITHSUNDEEP
sql-serverstored-procedures
giri-webdev
giri-webdev

Reputation: 535

Execute Stored Procedure against multiple tables

I want to execute below stored procedure against multiple tables, by passing table name as parameter. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
CREATE PROCEDURE GETARTICLESPAGEWISE
  @PageIndex INT = 1
  ,@PageSize INT = 10
  ,@ArticleCategory varchar(100) = 'null'
  ,@RecordCount INT OUTPUT

 AS
 BEGIN
  SET NOCOUNT ON;
  SELECT ROW_NUMBER() OVER
  (
        ORDER BY [ArticleID] ASC
  )AS RowNumber
  ,[ArticleID]
  ,[ArticleName]
  ,[ArticleCategory]
 INTO #ResultSet
  FROM [ASPDOTNETARTICLES]
  WHERE ArticleCategory = @ArticleCategory
  SELECT @RecordCount = COUNT(*)
  FROM #ResultSet

  SELECT * FROM #ResultSet
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) +                @PageSize) - 1

  DROP TABLE #ResultSet
 END
GO


--EXEC dbo.GETARTICLESPAGEWISE @PageIndex =1, @PageSize  = 2,          @ArticleCategory='Gridview',@RecordCount=0

I tried but error occurred, tried stored procedure

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE GETARTICLESPAGEWISEGENERIC
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@ArticleCategory varchar(100) = 'null'
      ,@RecordCount INT OUTPUT
      ,@TableName varchar(100) = 'NULL'
AS
BEGIN
DECLARE @columnList varchar(200)
SET @columnList='  SELECT ROW_NUMBER() OVER
      (
            ORDER BY [ArticleID] ASC
      )AS RowNumber
      ,[ArticleID]
      ,[ArticleName]
      ,[ArticleCategory]'

      SET NOCOUNT ON;
DECLARE @sqlCommand varchar(300)

set @sqlCommand ='
    SELECT '+ @columnList +'
     INTO #ResultSet
      FROM '+ @TableName +'
      WHERE ArticleCategory ='+ @ArticleCategory+'
      //i got the error in this statement
        SELECT @RecordCount = COUNT(*)
      FROM #ResultSet

      SELECT * FROM #ResultSet
      // i got the error in this statement 
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

      DROP TABLE #ResultSet'
END
GO

Can u give solution for this problem?

Upvotes: 3

Views: 1585

Answers (2)

Devart
Devart

Reputation: 121902

Try this one -

CREATE PROCEDURE GETARTICLESPAGEWISE 

       @PageIndex INT = 1
     , @PageSize INT = 10
     , @ArticleCategory VARCHAR(100) = 'null'
     , @TableName VARCHAR(100) = 'dbo.ASPDOTNETARTICLES'
     , @RecordCount INT OUTPUT

AS BEGIN

     SET NOCOUNT ON;

     IF OBJECT_ID (N'tempdb.dbo.##ResultSet') IS NOT NULL
        DROP TABLE ##ResultSet

     DECLARE @SQL NVARCHAR(MAX) = '
     SELECT
           RowNumber = ROW_NUMBER() OVER (ORDER BY ArticleID) 
         , ArticleID
         , ArticleName
         , ArticleCategory
     INTO ##ResultSet
     FROM ' + @TableName + '
     WHERE ArticleCategory = ''' + @ArticleCategory + ''''

     EXEC sys.sp_executesql @SQL

     SELECT @RecordCount = COUNT(*)
     FROM ##ResultSet

     SELECT *
     FROM ##ResultSet
     WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND (((@PageIndex - 1) * @PageSize + 1) + @PageSize) - 1

END

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13486

create a table which will hold all the table names to be processed and loop through this table inside your sproc.

Upvotes: 0

Related Questions