Very Computer

1. 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.

2. Italian VB5? It's only virtual!

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

4. SQL 7 quote help!

5. Dynamic Temp Table

6. SQL Stored Procedures...

7. Dynamic temp tables

8. SQL SERVER 7 gives me strange JDBC error.

9. Sybase Dynamic Temp Tables

10. Dynamic temp tables

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

12. Temp tables in dynamic SQL?

13. Dynamic SQL + local temp tables