DataType TABLE questions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: WSCA Admin (google_at_walkersca.com)
Date: 04/30/04 

Date: Thu, 29 Apr 2004 21:57:55 -0700

I have a database with millions of records, and a SP that grabs about 5000
records at a time. The same SP is executed each time but it can not select
the same 5000 records. This is what I did.

Create a table with an ID which holds all the records that have been
grabbed. Then I grab the records making sure I did not grab the ones in
that table I created with the ID as the name and put them into a #temp
table. Then I return the 5000 records to the client. then I take all the
records in the #temp table and insert them into the table with the ID as the
name.

AS YOU CAN imagine this is very intensive to the I/O. So I thought of at
leased eliminating the #temp table and use a TABLE data type. My problem is
that I must execute a query that is generated by parameters. So creating a
dynamic string (declared as @str_sp) and then using execute (@str_sp)
doesn't work with data type TABLE's. It always says I must declare the
variable @TempTable (what I called it).

Example:

Note: Two input vars come from the SP (@ID and @Total)

DECLARE @TempTable TABLE (IPAddress varchar(32), DataStamp smalldatetime)
DECLARE @str_sp varchar(1000)

/* because I use vars in my TSQL I must do the following (right?)

SELECT @str_sp = 'INSERT INTO ' + @TempTable + ' SELECT TOP ' +
convert(varchar, @Total) + ' IPAddress, DateStamp FROM ..... blah blah

exec (@str_sp)

An error pops up saying I must declare @TempTable? I did??? now if I don't
include the insert into the table that I created with the data type it works
fine. So can you not use Data Type TABLE in a exec like that? And if you
want to use it with a query that is dynamic like the one above how would you
do it? Its late so hopefully I made some since..

thanks good night

Relevant Pages

  • Re: Trying to creatively work around 8000 Character limit with sp_
    ... I think sp_execresultset expects a nvarchar, so the string will be truncated ... create table dbo.t (colA int not null unique, ... declare @str2 varchar ... execute master..xp_cmdshell @cmd, no_output ...
    (microsoft.public.sqlserver.programming)
  • Re: possible new irc worm
    ... > I attempted to grab that package in order to take a look at it, ... the IP seems to be dead. ... Presumably it uses this to execute the embedded executable. ... world's premier technical IT security event! ...
    (Incidents)
  • RE: SQL Server not inserting all rows from single long statement
    ... >From Books Online explaination of EXECUTE: ... or nvarchar data type with a maximum value of the server's available ... characters for char or varchar data type strings. ... If the string is greater ...
    (microsoft.public.sqlserver.server)
  • Batch with cursor successful, but not really
    ... I noticed that if I executed the same batch twice ... could then execute the batch. ... DECLARE @Timestamp datetime ... DECLARE @SitePrefix varchar ...
    (microsoft.public.sqlserver)
  • Batch with cursor successful, but not really
    ... I noticed that if I executed the same batch twice ... could then execute the batch. ... DECLARE @Timestamp datetime ... DECLARE @SitePrefix varchar ...
    (microsoft.public.sqlserver)