Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    20

    Unanswered: dynamic query not executing

    Hi all,

    create procedure dbo.runquery
    as
    begin
    select tempkey = identity(6), empl_id into #temptable from empl
    select #temptable.tempkey, #temptable.empl_id from #temptable
    end

    The above stored procedure is working..But when i tried the same with dynamic query as below it is not working properly ..why ..Some one plzz help me


    create procedure dbo.runquery
    as
    begin
    DECLARE @tempquery varchar(100), @tempquery2 varchar(100)
    select @tempquery = "select tempkey = identity(6), empl_id into #temptable from empl"
    exec (@tempquery)
    select @tempquery2 = "select * from #temptable"
    exec(@tempquery2)

    end

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    CREATE PROCEDURE runquery
    AS
    BEGIN
        DECLARE @tempquery varchar(255)
    
        select @tempquery = "select tempkey = convert(identity(6), empl_id) from empl" 
        exec (@tempquery)
    END
    or better
    Code:
    CREATE PROCEDURE runquery
    AS
    BEGIN
        DECLARE @tempquery varchar(255)
        select @tempquery = "select empl_id from empl" 
        exec (@tempquery)
    END
    or better still
    Code:
    select empl_id from empl

  3. #3
    Join Date
    Apr 2008
    Posts
    20
    mike i want to create a temporary table....then what is the solution???

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The temp table is just that - temporary. Why not create a permanent table and then use your dynamic sql to insert the rows you want ie

    Code:
    -- create a table (once)
    select empl_id into TempIds from empl
    
    -- create the sproc
    CREATE PROCEDURE runquery
    AS
    BEGIN
        DECLARE @tempquery varchar(255)
    
        delete TempIds
    
        select @tempquery = "insert TempIds (empl_id ) select empl_id from empl" 
        exec (@tempquery)
    END
    
    -- run the sproc
    exec runquery
    
    -- grab the ids
    select * from TempIds
    Often it's best to say what it is you're trying to do as there may well be better approaches to certain problems.

  5. #5
    Join Date
    May 2007
    Posts
    4
    Temporary #tables created inside exec (or execute-immediate) statement are dropped automatically when exec-immediate returns. so, #tables therefore can't be accessed from outside the exec-immediate statement.

    you can test this by writing one more atement (i.e select name, type, crdate from tempdb..sysobjects where name like '%#') in your proc wright after exec(@tempquery). that sql statement returns 0 rows for u. that explains how #tables drops automatically when exec-immediate returns.

    so, you better of creating temp tables outside exec-immediate and accss them inside exec-immediate query. make sense ?

  6. #6
    Join Date
    Apr 2008
    Posts
    20
    bhavan..thanx for reply..can u please tell me whatz the best solution for this case..

    Below i written a stored procedure ,which is generic for pagination..



    create procedure dbo.pagination
    (@query varchar(500),@pagesize INT,@pagenumber INT)
    as
    begin
    IF @pagenumber = 1
    BEGIN
    SET ROWCOUNT @pagesize
    exec (@query)
    END
    IF @pagenumber > 1
    BEGIN
    DECLARE @startpos INT,
    @position INT

    SELECT @position = @pagesize * @pagenumber
    SELECT @startpos = @position - @pagesize +1

    /*
    DECLARE @createquery varchar(200),
    @displayquery varchar(200)

    SET ROWCOUNT @position
    SELECT @createquery = "select tempkey = identity(6), * into #temptable from (" + @query + ")"
    exec(@createquery)

    SELECT @displayquery = "select * from #temptable where tempkey >= " + convert(varchar(6), @startpos) + "and tempkey <= "+ convert(varchar(6),@position)
    exec(@displayquery) */

    SET ROWCOUNT @position
    exec(@query)
    END
    end


    for the first page i am getting the rows exactly with the page size..when coming to the next pages..with rowcount i am not able achieve that.. means i am getting the rows including the previous pages..To solve this , i tried to create a temporary table(which i commented )..

    Now i understood..why i am facing problem with that temporary table, from the reply of urs..

    so any suggestion..for my pagination stored procedure

  7. #7
    Join Date
    May 2007
    Posts
    4
    ok, first things are first - as i said earlier - #tables created inside exec-immedate are automatically dropped when exec returns. So, don't use exec-immediate statement for temp table creation.

    second thing is, i think the reason why you get the previous rows as well in ur result set is because : tempkey >= " + convert(varchar(6), @startpos).

    try : tempkey > instead tempkey >= and check ur results.

  8. #8
    Join Date
    Apr 2008
    Posts
    20
    bhavan..actually with the commented code i am not getting any rows..for the page greater than 1..

    the current code if u execute.for the page greater than one it gives previous pages rows also..

    now..i don't bather about the commented code..suggest me..for the actuall code..how to limit the rows..

    or u have any pagination stored procedure,which is generic..

  9. #9
    Join Date
    Apr 2008
    Posts
    20
    Code:
    create procedure dbo.pagination
    (@query varchar(500),@pagesize INT,@pagenumber INT)
    as
    begin
       IF @pagenumber = 1
       BEGIN
          SET ROWCOUNT @pagesize
          exec (@query)
       END
       IF @pagenumber > 1 
       BEGIN   
           DECLARE @startpos INT,
                   @position   INT
        
           SELECT @position = @pagesize * @pagenumber
           SELECT @startpos = @position - @pagesize +1     
              
           SET ROWCOUNT @position      
           exec(@query) 
        END
    end
    if u execute the above stored procedure ..for example

    Code:
        exec pagination 'select * from empl' , 5 , 2
    i will get 10 rows....i want the rows from 6th to 10th..what should i do..for this..

    for the first page , i will get the proper result

Posting Permissions

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