Dynamic SQL accessing dynamic temp tables 
Author Message
 Dynamic SQL accessing dynamic temp tables

I posted a problem last week about running a dynamically built SQL statement
against a table variable and received a helpfull response from Tobias Thernstrom.
He suggested using a temporary table instead of a table variable as follows :
****************************************************
        Owen,

        The problem is that the EXEC-statement runs in a
        different batch from the proc. itself, this means that any vars. declared
in
        the proc.
        will be out of scope in the batch. So the only change you would need to do
        is
        to create a temp. table instead of a table var.

        Ex (this is your second last script, slitely modified).

        CREATE proc spcompanytest
        as
        CREATE TABLE #tmptable (test_co_no int, flag char)



        company_number = 45 or company_number = 46'


        select company_number, 'Y' from test_company




        Hope this helps !

        /Tobias
****************************************************
The problem I now have is that my stored procedure may be being run many times at
once by different web-users querying the database, I presume that there may well
be contention with the temporary table as it will have the same name for all
procedures. I thought that I might be able to get around the problem by creating
the tablename from the user-id that the user logs into the intranet as, but I get
the same problem as I initally started out with (allbeit from a different angle).
I can't dynamically create a temporary table from a parameter input into the
stored procedure _and_ use it with my dynamicaly built SQL.

Any further help would be a real bonus.

Cheers,

Owen.



Fri, 26 Sep 2003 16:05:39 GMT
 Dynamic SQL accessing dynamic temp tables

Owen,

A local temp table (one created with a single #) will only be seen by the
session or Batch that created it. It is completely safe to use this  in
concurrent store procedures.

--
Andrew J. Kelly
Targitmail.com


I posted a problem last week about running a dynamically built SQL statement
against a table variable and received a helpfull response from Tobias
Thernstrom.
He suggested using a temporary table instead of a table variable as follows
:
****************************************************
Owen,

The problem is that the EXEC-statement runs in a
different batch from the proc. itself, this means that any vars. declared
in
the proc.
will be out of scope in the batch. So the only change you would need to do
is
to create a temp. table instead of a table var.

Ex (this is your second last script, slitely modified).

CREATE proc spcompanytest
as
CREATE TABLE #tmptable (test_co_no int, flag char)



company_number = 45 or company_number = 46'


select company_number, 'Y' from test_company




Hope this helps !

/Tobias
****************************************************
The problem I now have is that my stored procedure may be being run many
times at
once by different web-users querying the database, I presume that there may
well
be contention with the temporary table as it will have the same name for all
procedures. I thought that I might be able to get around the problem by
creating
the tablename from the user-id that the user logs into the intranet as, but
I get
the same problem as I initally started out with (allbeit from a different
angle).
I can't dynamically create a temporary table from a parameter input into the
stored procedure _and_ use it with my dynamicaly built SQL.

Any further help would be a real bonus.

Cheers,

Owen.



Fri, 26 Sep 2003 20:14:23 GMT
 Dynamic SQL accessing dynamic temp tables



Quote:
> A local temp table (one created with a single #) will only be seen by the
> session or Batch that created it. It is completely safe to use this  in
> concurrent store procedures.

Ah,how easily we forget.If server doesn't find the table how do you
really know it won't search all users for a similar table:).

stevie



Sat, 27 Sep 2003 00:07:29 GMT
 Dynamic SQL accessing dynamic temp tables

Quote:
> Ah,how easily we forget.If server doesn't find the table how do you
> really know it won't search all users for a similar table:).

Your right, I think we should run each query on a separate machine just in
case. As a matter of fact I am going to suggest everybod get a server farm
for just these issues.<rofl>

--
Andrew J. Kelly
Targitmail.com


Quote:



> > A local temp table (one created with a single #) will only be seen by
the
> > session or Batch that created it. It is completely safe to use this  in
> > concurrent store procedures.
> Ah,how easily we forget.If server doesn't find the table how do you
> really know it won't search all users for a similar table:).

> stevie



Sat, 27 Sep 2003 00:56:49 GMT
 Dynamic SQL accessing dynamic temp tables
Nice suggestion - that should eat up the hardware budget nicely and keep everyone
else off my back :-)

Seriously, thanks for your help.

What I finally decided on (being of a cautious <sp?> nature) was to have a
permanent table to store the temporary data with an extra column of 'userid' in
each row - this can be passed into the stored procedure as an extra parameter and
I can clear out any old results for that particular userid before I process the
queries. Each userid can only run the stored procedure once at a time so that
should be as safe as I need (unless anyone knows different).

Thanks again for your help, I'm off to buy a few more servers.

Owen.  

Quote:
-----Original Message-----
> Ah,how easily we forget.If server doesn't find the table how do you
> really know it won't search all users for a similar table:).

Your right, I think we should run each query on a separate machine just in
case. As a matter of fact I am going to suggest everybod get a server farm
for just these issues.<rofl>

--
Andrew J. Kelly
Targitmail.com



Sat, 27 Sep 2003 01:29:06 GMT
 Dynamic SQL accessing dynamic temp tables

Quote:

>What I finally decided on (being of a cautious <sp?> nature) was to have a
>permanent table to store the temporary data with an extra column of
>'userid' in each row - this can be passed into the stored procedure as an
>extra parameter and I can clear out any old results for that particular
>userid before I process the queries. Each userid can only run the stored
>procedure once at a time so that should be as safe as I need (unless
>anyone knows different).

You think that each userid only can...

We have a bunch of tables which basically are temp tables, but for
various reasons are created as permanent. The standarad procedure

connection, so there cannot be more than one of them at the same
time.


the table is accessed from ADO clients that uses disconnected
record sets. What we did was to have a special procedure that
generates a key which always is a negative number. Originally,
we just next the next negative number available with SELECT MIN,
but since we're on 6.5 we had a lot of conflicts since this
always left simultaneous processes on the same page. The current
scheme is to take the current time and use the milliseconds,
minutes, hours and the spid. There is a loop in case a generated
key should already be in use, but this is very unlikely.

--



Sun, 28 Sep 2003 06:10:48 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Dynamic Temp-Table in a dynamic Browse..

2. need help...Using @TABLE variable instead of #temp tables with dynamic SQL (sp_executesql)

3. Temp tables in dynamic SQL?

4. Dynamic SQL + local temp tables

5. Temp Tables via dynamic sql

6. Dynamic SQL with temp table probelm

7. Dynamic SQL vs Temp Tables

8. Dynamic Query w/ Dynamic Table Name (with a Northwind Example)

9. Dynamic Query w/ Dynamic Table Names

10. How to sort a dynamic table by dynamic field in sp

11. SQL Server 6.5 Dynamic SQL inside Trigger to Access deleted Table

12. Dynamic Temp Table


 
Powered by phpBB® Forum Software