Results 1 to 4 of 4

Thread: reporting

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2004
    Posts
    4

    Try this

    -- To create table test
    drop table test1
    go
    create table test1(
    clave int,
    desc_test1 char(20))
    go
    set nocount on
    declare @row int
    select @row = 0
    while @row < 100
    begin
    insert into test1 values (@row, 'valores ' + convert(char(5), @row ) )
    select @row = @row + 1
    end

    select * from test1

    ------------
    -- stored procedure to obtein reg's, whit scroll
    create proc cons1
    @pag int, -- page to obtein
    @scroll int -- number of data to obtein
    as
    declare @scroll_begin int,
    @scroll_end int
    create table #temp(
    id_temp int identity,
    clave int,
    desc_test1 char(20))

    insert into #temp
    select clave, desc_test1
    from test1
    -- order by xyz

    select @scroll_begin = (@pag*@scroll-(@scroll-1) )
    select @scroll_end = (@pag*@scroll)
    select *
    from #temp
    where
    id_temp between @scroll_begin and @scroll_end

    -- example
    exec cons1 1, 15
    exec cons1 2, 15
    exec cons1 3, 15

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    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))





    I wanted to select the result set of (exec(@stmt)) into #tmpreport table

    How can write the above SELECT statemt

  4. #4
    Join Date
    Jun 2004
    Posts
    4
    create table #tmpReport(
    id int identity,
    a1 ,
    a2,
    a3,
    a4,
    a5,
    a6,
    a7,
    a8)
    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))
    insert INTO #tmpReport
    exec(@stmt)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •