如何在存储过程中创建索引? [英] How do I create an index inside a stored procedure?

问题描述

如何在存储过程中创建索引?它抱怨

How do I create an index inside a stored procedure? It complains

消息 102，级别 15，状态 1，过程 createIndexModifiedOn，第 12 行
PRIMARY"附近的语法不正确.

Msg 102, Level 15, State 1, Procedure createIndexModifiedOn, Line 12
Incorrect syntax near 'PRIMARY'.

但是 ON [PRIMARY] 是 SQL Server 本身在创建新索引并选择 Script As New Query 时使用的.

But ON [PRIMARY] is what SQL Server itself uses if you create a new index and select Script As New Query.

如果我删除 ON [PRIMARY] 那么它会给出这个错误

If I remove ON [PRIMARY] then it gives this error

消息 102，级别 15，状态 1，过程 createIndexModifiedOn，第 12 行
')' 附近的语法不正确.

Msg 102, Level 15, State 1, Procedure createIndexModifiedOn, Line 12
Incorrect syntax near ')'.

流程如下:

create proc [dbo].createIndexModifiedOn
    @table char(256)
as begin
    declare @idx char(256)
    set @idx = 'idx_' + SUBSTRING(@table, 7, len(@table)-1) + '_modified_on';
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@table) AND name = @idx)
        DROP INDEX [@idx] ON [@table]

    CREATE NONCLUSTERED INDEX [@idx] ON [@table]
    (
        [modified_on] ASC
    ) ON [PRIMARY]
go

这最终是完整的查询:

create proc [dbo].createIndexModifiedOn
    @table varchar(256)
as 
    declare @idx varchar(256);
    declare @sql nvarchar(999);
    set @idx = 'idx_' + SUBSTRING(@table, 8, len(@table)-8) + '_modified_on';
    set @sql = '
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''' + @table + ''') AND name = ''' + @idx + ''')
        DROP INDEX [' + @idx + '] ON ' + @table + '

    CREATE NONCLUSTERED INDEX [' + @idx + '] ON ' + @table + '
    (
        [modified_on] ASC
    ) ON [PRIMARY] 
    ';
    print @table + ', ' + @idx;
    BEGIN TRY
        EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
        PRINT 'errno: ' + ltrim(str(error_number()))
        PRINT 'errmsg: ' + error_message()
    END CATCH
GO

EXEC sp_MSforeachtable 'exec createIndexModifiedOn "?"'

推荐答案

您不能在 CREATE INDEX 语句中使用变量.为此，您需要生成一个 SQL 字符串并使用 sp_executesql 执行它.

You can't use variables in the CREATE INDEX statement as you have. To do this, you will need to generate a SQL string and execute it with sp_executesql.

手绘示例:

DECLARE @sql NVARCHAR(1024);
SET @sql = 'CREATE NONCLUSTERED INDEX [' + @idx + '] ON [' + @table + ']
(
    [modified_on] ASC
) ON [PRIMARY];';
EXEC sp_executesql @sql;

这篇关于如何在存储过程中创建索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！