~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Owen Mortensen" <ojm@a...>

Subject: [asp_databases] Re: Stored Procedure table name as variable?





: How does that let me do a query on a table whose name I pass into the

: stored procedure as a paramter?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Couple of options would be:



Build your SQL string in your sproc and Exec() it

-or-

Use a CASE statement and pre-built queries



CREATE PROC usp_MySproc



    @TableName    varChar(100)



AS



    DECLARE SQLString    varChar(200)

    SET SQLString = "SELECT * FROM " + @TableName

    EXEC(SQLString)



or



CREATE PROC usp_MySproc



    @TableName    varChar(100)



AS



    CASE @TableName

    WHEN 'Table1'

        SELECT * FROM Table1

    WHEN 'Table2"

        SELECT * FROM Table2

    

etc



Cheers

Ken

This is a multi-part message in MIME format.



------=_NextPart_000_000D_01C1C5F6.27C2F780

Content-Type: text/plain;

	charset="us-ascii"

Content-Transfer-Encoding: 7bit



In a stored procedure, what is the syntax (if possible) to use a

variable as the table name?

 

TIA,

Owen

This is a multi-part message in MIME format.



------=_NextPart_000_0016_01C1C5FE.297A98F0

Content-Type: text/plain;

	charset="us-ascii"

Content-Transfer-Encoding: 7bit



??

 

How does that let me do a query on a table whose name I pass into the

stored procedure as a paramter?

 

Owen



-----Original Message-----

From: James Garrett [mailto:jhgarrett@e...] 

Sent: Thursday, March 07, 2002 3:40 PM

To: ASP Databases

Subject: [asp_databases] Re: Stored Procedure table name as variable?













Temporary Tables





You can create local and global temporary tables. Local temporary tables

are visible only in the current session; global temporary tables are

visible to all sessions.



Prefix local temporary table names with single number sign

(#table_name), and prefix global temporary table names with a double

number sign (##table_name).



SQL statements reference the temporary table using the value specified

for table_name in the CREATE TABLE statement:



CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)



INSERT INTO #MyTempTable VALUES (1)



If a local temporary table is created in a stored procedure or

application that can be executed at the same time by several users, SQL

Server has to be able to distinguish the tables created by the different

users. SQL Server does this by internally appending a numeric suffix to

each local temporary table name. The full name of a temporary table as

stored in the sysobjects table in tempdb consists of table name

specified in the CREATE TABLE statement and the system-generated numeric

suffix. To allow for the suffix, table_name specified for a local

temporary name cannot exceed 116 characters.








$subst('Email.Unsub'). 

This is a multi-part message in MIME format.



------=_NextPart_000_001A_01C1C5FF.FB5EA220

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



MessageExplain completely what you are trying to do.



James

  ----- Original Message -----

  From: Owen Mortensen

  To: ASP Databases

  Sent: Thursday, March 07, 2002 7:33 PM

  Subject: [asp_databases] Re: Stored Procedure table name as variable?





  ??



  How does that let me do a query on a table whose name I pass into the 

stored procedure as a paramter?



  Owen

    -----Original Message-----

    From: James Garrett [mailto:jhgarrett@e...]

    Sent: Thursday, March 07, 2002 3:40 PM

    To: ASP Databases

    Subject: [asp_databases] Re: Stored Procedure table name as 

variable?





    Temporary Tables

    You can create local and global temporary tables. Local temporary 

tables are visible only in the current session; global temporary tables 

are visible to all sessions.



    Prefix local temporary table names with single number sign 

(#table_name), and prefix global temporary table names with a double 

number sign (##table_name).



    SQL statements reference the temporary table using the value 

specified for table_name in the CREATE TABLE statement:



    CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)



    INSERT INTO #MyTempTable VALUES (1)



    If a local temporary table is created in a stored procedure or 

application that can be executed at the same time by several users, SQL 

Server has to be able to distinguish the tables created by the different 

users. SQL Server does this by internally appending a numeric suffix to 

each local temporary table name. The full name of a temporary table as 

stored in the sysobjects table in tempdb consists of table name 

specified in the CREATE TABLE statement and the system-generated numeric 

suffix. To allow for the suffix, table_name specified for a local 

temporary name cannot exceed 116 characters.






$subst('Email.Unsub').




$subst('Email.Unsub').

This is a multi-part message in MIME format.



------=_NextPart_000_000A_01C1C5FF.20064A20

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Message

Temporary Tables

You can create local and global temporary tables. Local temporary tables 

are visible only in the current session; global temporary tables are 

visible to all sessions.



Prefix local temporary table names with single number sign 

(#table_name), and prefix global temporary table names with a double 

number sign (##table_name).



SQL statements reference the temporary table using the value specified 

for table_name in the CREATE TABLE statement:



CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)



INSERT INTO #MyTempTable VALUES (1)



If a local temporary table is created in a stored procedure or 

application that can be executed at the same time by several users, SQL 

Server has to be able to distinguish the tables created by the different 

users. SQL Server does this by internally appending a numeric suffix to 

each local temporary table name. The full name of a temporary table as 

stored in the sysobjects table in tempdb consists of table name 

specified in the CREATE TABLE statement and the system-generated numeric 

suffix. To allow for the suffix, table_name specified for a local 

temporary name cannot exceed 116 characters.