You can't use a variable for a table or column name. However, you can dynamically create a SQL statement and execute it.
Create Procedure CreateNewIndex
@mytable nvarchar(40),
@indexname nvarchar(20),
@columns nvarchar(60) As
Declare @sql nvarchar(500)
Set @sql=
'CREATE INDEX ' + @indexname +
' ON ' + @mytable +
'(' + @columns + ')'
Execute @sql
----------------------
Stored procedure call:
Exec CreateNewIndex 'tblAddresses', 'LastName, FirstName' Terry L. Broadbent FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
Thanks for the quick response. I am not sure I stated clearly what I was asking for so please let me elaborate. If I have the following...
CREATE PROCEDURE a
AS
BEGIN
DECLARE @mytable TABLE (col_a INT)
INSERT INTO @mytable
SELECT col_1
FROM any_other_table
SELECT * FROM @mytable
END
This is all legal so far.
If I include the following line...
CREATE PROCEDURE a
AS
BEGIN
DECLARE @mytable TABLE (col_a INT)
CREATE INDEX i ON @mytable(col_a)
INSERT INTO @mytable
SELECT col_1
FROM any_other_table
SELECT * FROM @mytable
END
I get the syntax error. Is there a way to create the index i? I suppose I could create col_a as a primary key and that would do for this example but more importantly I want to be able to create an index on a table variable.
I apologize for the misundersanding. You must be using SQL 2000 and the table variable. We are just converting to SQL 2000 so I'm not familiar with table variables.
The following information from SQL BOL should answer your questions.[ul]Table variables and user-defined functions that return a table can be used only in certain SELECT and INSERT statements, and where tables are supported in the UPDATE, DELETE, and DECLARE CURSOR statements. table variables and user-defined functions that return a table cannot be used in any other Transact-SQL statements.
Indexes or other constraints applied to the table must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannot be applied later, because the CREATE INDEX or ALTER TABLE statements [ul]cannot reference[/u] table variables and user-defined functions.[/ul]
Are you adding a large number of rows to the table? Are you joining the table to another table? What is the purpose of the index? Inserts should be faster without an index. Indexing won't do much to improve select performance unless the number of records is quite large. Terry L. Broadbent FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
Thanks for the feedback. I am using SQL 2000. As for the usage, I am inserting about 500,000 records into the table. I have a couple of insert statements then go on to reference the table several more times. I read that table variables are more efficient and was trying to take advantage of that fact. I just converted over to a temp table (#mytable) and built the index after the inserts. In doing so I cut my run time in half (20 minutes down to 10 minutes). Hopefully a future release will include the ability to build an index post creation of the table variable. Thanks again for the information.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.