July 24, 2003 at 3:21 am
The following code is intended to provide a server side solution for outputing data in pages given any valid SQL statement, the start row and the number of rows to return.
Could someone please suggest how to change this code to use a temp table that is unique for a given connection?
I tried using #temp_1 so the table would be deleted if the connection dropped but it would not work. I could possibly use a function to provide a unique string for the name of the temp table but that does not seem to be the best solution.
I would also be interested in any examples of how this script could be improved.
CREATE PROCEDURE mp_paged_data
@sql nvarchar(2000),
@start_row int = 1,
@row_limit int = 10,
@total_rows int = 0 OUTPUT
AS
-- This works, however code is creating a live temp_1 table in the database
-- instead of using a #temp_1 temporary table because is just would not work
-- Better solution maybe to get data into a cursor and loop through it until
-- desired page of records is found
SET NOCOUNT ON
-- Run SQL passed as parameter and place in temp table
SET @sql = REPLACE(@sql,'FROM ' , 'INTO temp_1 FROM ')
EXECUTE(@sql)
--PRINT @sql
SET @total_rows = @@ROWCOUNT
-- Add column to determine row numbers
ALTER TABLE temp_1
ADD row_num int IDENTITY NOT NULL UNIQUE
-- Copy into intermediate table
-- as row_num was not recognised as a valid column
-- for a WHERE clause from the first temp table
SELECT *
INTO #temp_2
FROM temp_1
-- Delete temp table from memory
DROP TABLE temp_1
-- Select desired page based on
-- StartRow and NumRows
SET ROWCOUNT @row_limit
SELECT * FROM #temp_2
WHERE row_num >= @start_row
ORDER BY row_num
-- Delete temp table from memory
DROP TABLE #temp_2
GO
July 24, 2003 at 5:56 am
Hi Dion!
You can store the result from a dynamic sql in a temporary table and return it to the user with a simple select on the temporary table. But you need to use a global temporary table (##myTable), not local temporary table (#myTable).
If you execute the following code:
DECLARE@SQLNVARCHAR(2000)
SELECT@SQL = 'SELECT myCol1 INTO #myTemp FROM myTable'
EXECUTE(@SQL)
SELECT*
FROM#myTemp
You will get the following result:
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#myTemp'.
The temporary table #myTemp will be owned of the EXECUTE-statement not by your batch, and only live until the EXECUTE is done. The EXECUTE then return a value if the execute was successful of not.
If you execute the following code:
DECLARE@SQLNVARCHAR(2000)
SELECT@SQL = 'SELECT myCol1 INTO ##myTemp FROM myTable'
EXECUTE(@SQL)
SELECT*
FROM##myTemp
You will get the result of the ##myTemp table. This is because the global temporary table will live until nothing in the batch uses that table or connection is dropped.
Best of luck,
robbac
___the truth is out there___
robbac
___the truth is out there___
July 24, 2003 at 6:57 am
DECLARE @sql1 nvarchar(4000)
SET @sql1 = @sql
SET @sql1 = REPLACE(@sql1,'SELECT' , 'SELECT IDENTITY(int,1,1) as row_num,')
SET @sql1 = REPLACE(@sql1,'FROM ' , 'INTO #temp FROM ')
SET @sql1 = @sql1 + ' ' + LEFT(@sql,CHARINDEX(' FROM ',@sql)+5)
SET @sql1 = @sql1 + '#temp WHERE row_num >= '+CAST(@start_row as varchar)
SET @sql1 = @sql1 +' AND row_num < '+CAST(@start_row+@row_limit as varchar)
EXEC sp_executesql @sql1
Far away is close at hand in the images of elsewhere.
Anon.
July 25, 2003 at 5:18 am
hi!
why don't use something like that (no need to create a temporary table) - hope i really understood what you want to achieve:
create procedure sel_from_table_limited
@p_start_row int,
@p_row_limit int,
@p_row_count int output
as
declare @v_sql varchar(250)
set nocount on
select @v_sql = 'select top ' + cast(@p_row_limit as varchar) + ' *'
' from your_table where primary_key not in (' +
'select top ' + cast(@p_start_row as varchar) + ' primary key' +
' from your_table order by primary_key)' +
' order by primary_key'
execute(@v_sql)
select @p_row_count = @@rowcount
go
best regards,
chris.
Edited by - cneuhold on 07/25/2003 05:20:54 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply