Posted by Erland Sommarskog on 12/08/06 23:00

frien (001frien@gmail.com) writes:
> This is what i did
> this gave no synatx error..............but is not doing what it is
> suppose to do..
> if i execute the query alone that is working fine....but am not able to
> implement as a stored procedure
>
>
> CREATE PROCEDURE [dbo].[sd_find_create_ind]
> @tblName varchar(255),
> @colName varchar(255),
> @indName varchar(255)
> AS
> declare @query varchar(1024)
> select @query = 'IF indexproperty(object_id('+@tblName+'),
> '+@indName+', ''IsClustered'') IS NULL
> CREATE INDEX '+@indName+' ON '+@tblName+'('+@colName+')'
> GO

There is no EXEC(@query), so I guess that's why the procedure is not
doing anything. But it would be simpler to do it as:

IF indexproperty(object_id(@tblName), @indName, 'IsClustered') IS NULL
BEGIN
SELECT @q = 'CREATE INDEX ' + quotname(@indName) + ' ON ' +
quotename(@tblName) + '(' + quotename(@colName) + ')'
EXEC(@q)
END

The quotename() is good for protection against SQL injection.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: set default precision on decimal type?
• Prev in forum: Re: help plz!!! stored procedure to find index if not found create a index
• Thread view: Re: help plz!!! stored procedure to find index if not found create a index

[Reply to this message]