reporting
-
I have 50 rows in my sumary table.I wantted to show them in the front end with pagination,means i wanted to show those 50 rows in 5 different pages with 10 rows per page.
How can i do that using sql stored procedure.
-
Firstly pass your stored procedure the page number that you wish to view, and optionally the number of items per page. With your summary table, insert the results into a temp table but add an identity column that seeds at one and increments by one (so should count from 1 to 50). Then simply have a where clause in the TSQL that selects the rows from (PageNumber * 10) to (PageNumber * 10)+10.
- --------------------------------------------------------
- alter proc dbo.PagedReport (
- @CurrentPage int,
- @ItemsPerPage int = 10 -- default this to 10 items per page
- )
- as
- declare @StartItem int
- declare @EndItem int
- set @StartItem = @CurrentPage * @ItemsPerPage
- set @EndItem = @StartItem + @ItemsParPage
- select IDENTITY(int, 1, 1) as RepID, * into #tmpReport from dbo.ReportTable
- select * from #tmpReport where RepId>=@StartItem and RepId<@EndItem
- --------------------------------------------------------
-
IF @alpha='0'
BEGIN
SELECT @sqlstmt='SELECT a1,a2,a3,a4,a5, a6,a7, a8
FROM #emp_temp order by ' +@col_name+' '+@orderby
END
ELSE
BEGIN
SELECT @sqlstmt='SELECT a1,a2,a3,a4,a5, a6,a7, a8
FROM #emp_temp WHERE emp_name like '''+@alphabet+''' order by ' +@col_name+' '+@orderby
END
SELECT IDENTITY(int, 1, 1) AS ID, * INTO #tmpReport FROM (exec(@stmt))
How can write the above statemt
-
One way of doing what you want is using several dynamic SQL statements. To reference temp tables from different procedures you need to reference them as global (## instead of just #), so tables can be referenced within dynamic sql.
------------------------------------------
- IF @alpha='0'
BEGIN
SET @sqlstmt='SELECT IDENTITY(int, 1, 1) AS ID a1,a2,a3,a4,a5, a6,a7, a8 INTO ##tmpReport FROM ##emp_temp order by ' +@col_name+' '+@orderby
END
ELSE
BEGIN
SET @sqlstmt='SELECT IDENTITY(int, 1, 1) AS ID a1,a2,a3,a4,a5, a6,a7, a8 INTO ##tmpReport FROM ##emp_temp WHERE emp_name like '''+@alphabet+''' order by ' +@col_name+' '+@orderby
END
- exec(@sqlstmt)
- SET @sqlstmt='SELECT * FROM ##tmpReport WHERE ID BETWEEN ' + CAST(@startID as varchar) + ' AND ' + CAST(@endID as varchar)
- exec(@sqlstmt)
- SET @sqlstmt='DROP TABLE ##tmpReport'
- exec(@sqlstmt)
------------------------------------------
- IF @alpha='0'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply