dynamic query not executing

mohanrao · #1 (**permalink**) 05-02-08, 02:43

Hi all,

create procedure dbo****nquery
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****nquery
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

mike_bike_kite · #2 (**permalink**) 05-02-08, 04:45

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

mohanrao · #3 (**permalink**) 05-02-08, 04:54

mike i want to create a temporary table....then what is the solution???

mike_bike_kite · #4 (**permalink**) 05-02-08, 05:24

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.

bhavan · #5 (**permalink**) 05-02-08, 06:10

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 ?

mohanrao · #6 (**permalink**) 05-02-08, 06:24

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

bhavan · #7 (**permalink**) 05-02-08, 07:05

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.

mohanrao · #8 (**permalink**) 05-02-08, 07:10

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

mohanrao · #9 (**permalink**) 05-02-08, 07:13

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

Internet Services

Web Development

Digital Marketing

Consumer Tech