Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-31 : 21:05:00
|
Hemant writes "Hi,
I am trying ot return 10000 records from my Database.
I need to return a configurable number of records at a time to the user. I followed the tips on your site to create a temprory table, adding the whole data to it along with a unique ID and finally querying of the temprory table to get the records I need to send back to the client. However this means that the whole process repeats every time the next row of records are requested. This Increases the CPU time for the Database. I am not sure how to do the caching of this data on the server side.
Also I want to make a generalized Stored Procedure to work for any kind of query. This means that I have to dynamically create the Table in the Stored Procedure. I am not sure how to proceed.
Here is the code that I have written
CREATE PROCEDURE newtable
(
@absP int,
@NumRec int,
@myCur as Varchar(240)
)
AS
SET NOCOUNT ON
Declare @start int
Declare @count int
Declare @TotalCount int
Declare @col1 varchar(50)
Declare @col2 varchar(50)
Declare @cur1 varchar(240)
Select @cur1 = "DECLARE MyCursor SCROLL CURSOR FOR " + @mycur
Exec(@cur1)
CREATE TABLE #tempItems
(
ID int Identity,
firstCol Varchar(50),
secCol VarChar(50)
)
Open MyCursor
Select @totalCount = @@Cursor_Rows
Select @start = @absP * @NumRec + 1
Select @count = 0
FETCH ABSOLUTE @start From MyCursor
INTO @col1, @col2
While @@Fetch_Status = 0
Begin
Select @count = @count + 1
INSERT into #tempItems(firstCol, secCol) VALUES (@col1, @col2)
If @count = @NumRec
Begin
Break
End
Fetch Next From MyCursor
INTO @col1, @col2
End
Close MyCursor
DEALLOCATE MyCursor
Select * from #tempItems
SET NOCOUNT OFF
In my code I have specified that there will be two columns. However I want to be able to create the Temprory table on the fly to make the stored procedure generalized. Can you help me out
Thanks
Hemant" |
|
|
|
|
|
Creating Dynamic & Temprory Tables in Stored Procedures