Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!"

quote:Graz, please tell me how because the reason for my coming here tonight was to find the solution for that exact problem.

I've already created the procedure.

I have done exactly what you are suggesting!It works when i use query analyzer to test it but it doesn't work when i execute the procedure from a remote location.

If you know why this is i would appreciate your help.

Thanks, Lloyd Cormier.

Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!"<P>Article <a href="/item.asp?ItemID=2029">Link</a>.

quote:I mean i have created a client application that connects to the server from another computer over the internet. When i use ado to execute the stored procedure in the client, it doesn't return anything. I'm thinking it is because in the timeframe it would take to transfer the contents of the temp table to the remote client, the temp table has already been dropped on the server.And so it retrieves an empty table.

If you haven't closed the connection, the temp table is not gonna get dropped. Your problem is most likely caused by the "Records Affected" messages that your stored procedure sends back to the client (ADO sees them as closed recordsets). So... If you can change your stored procedure, add SET NOCOUNT ON statement at the beginning and SET NOCOUNT OFF at the end of it. This will ensure that rowcounts don't get sent back to the client. If you aren't allowed to change the stored procedure, you can attack this problem from the client side. Use NextRecordset method of your recordset in a loop, until you find an open recordset.

We have a temp table that holds data for either 1 or many clients. Retrieval of data in permanent tables which we link to the temp table is slow in certain cases and we wish to add an index to the temp table (initial tests have show a large performance increase with the index!), however the temp table is referred to in stored procedures.

The temp table is created in our front end application (therefore outside of the scope of the stored procedure)

If an index is created on the temp table will the stored procedure use this when executing. I know that execution plans are created at the creation time of the stored procedure to speed up retrieval / processing time but seen as the temp table and the temp tables index will differ each time how does SQL deal with it?

Some options that I have rattling in my brain is the use of 'with recompile' as a stored procedure option and create the index outside of the sproc or will using named indexes override the need for this.

With v7+ the SPs are compiled at first run not at creation time. Creation just performs syntax checksWith v7+ if a temp table is accessed within a stored procedure but not created in it then the SP will be recompiled on each run.The SP cannot guarantee the structure of the temp table so has to be recompiled on each run.Given this all the SPs should use the index if useful.Note that as all the SPs that use the table will be recompiled on each run then this will degrrade performance and you could end up with contention on system tables.

==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy.

I have a very similar problem to this except I'd like the temporary table to hold some copied data, then give the user the option to edit it (using the datalist control in ASP.NET), then update the data in the temporary table, and when ready, have them submit it to a final table. Does anyone know if I could do this all in one stored procedure or would I have to have this done in separate ones? Is this even possible? Any help would greatly be appreciated.

Great article... but you may want to update the article a bit because I think there's a couple of misperceptions on your part... You wrote (and I mean NO disrespect)...

quote:If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

The red part is what I have an issue with... please read the following Microsoft article on Temp Tables and Table Variables paying particular attention to Q3/A3 and Q4/A4...

... considering that table variables cannot be created using a SELECT/INTO, they cannot be made to use statistics, they cannot be made to use non-constraint/keyed indexes, they cannot be referenced in nested scope, and the fact that they spool to disk (TempDB) if they get too big, one has to ask why you said they are more flexible than Temp Tables.

Further, Temp Tables also live in memory, just like table variables, until they too get too big at which time they spool to TempDB. They also allow creation using SELECT/INTO, do have statistics, and can use non-key indexes AND constraints. Still further, they "persist" in a QUERY ANALYZER or SSMS session (unlike table variables) so that you can do ad-hoc selects from them for troubleshooting and other analysis and they can be accessed in nested scope.

What about all the recompiles that Temp tables cause? Well, apparently, that's a bloody myth. They state in the following URL...

When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled. This is because during initial compilation, the temporary table does not exist and the compilation of this query is deferred until execution time. A compiled plan for the stored procedure is generated, but is incomplete. At execution time, the temporary table is created, and the select and insert statement are compiled. Since the stored procedure is already in execution, this compilation of the select and insert query are classified as a recompilation. It is important to note that in SQL Server 2005, only the select and insert statement in the stored procedure are recompiled. In SQL Server 2000, the entire stored procedure is recompiled. Subsequent re-executions of this stored procedure do not result in any more recompiles since the compiled plan is cached. Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used. Now consider a case when the temporary table is referenced in a second stored procedure as below:

The beauty of the article (MSDN Blog, really) is that they have all the code to backup what they say.

To me, the only advantage a Table Variable has over a Temp table, is that a UDF cannot use a Temp Table. If they removed that particular advantage, I'd never use a Table variable...

... of course, I could be wrong...

Thanks for "listening". Again, I've not meant any disrespect... this is a great forum with a great "core" of people and you're one of the best.

I'm able to see why some temp table objects in a script I'm debugging aren't going to work and I can rapidly do something about it. This presentation of information allows me to find this as a top link, get my question answered and get something done.