Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Stored procedure

Author

Topic

yslatha
Starting Member

8 Posts

Posted - 2008-08-13 : 11:13:22

I am creating below stored procedure and i geeting error
Msg 102, Level 15, State 1, Procedure usp_Detail_Index_Stats, Line 34
Incorrect syntax near '@table_name'.

please somobody help me

Create PROCEDURE [dbo].[usp_Detail_Index_Stats] @table_name sysname
AS

----------------------------------------------------------------------------------
-- ******VARIABLE DECLARATIONS******
----------------------------------------------------------------------------------
DECLARE @IndexTable TABLE
(
[Database] sysname, [Table] sysname, [Index Name] sysname NULL, index_id smallint,
[object_id] INT, [Index Type] VARCHAR(20), [Alloc Unit Type] VARCHAR(20),
[Avg Frag %] decimal(5,2), [Row Ct] bigint, [Stats Update Dt] datetime
)

DECLARE @dbid smallint --Database id for current database
DECLARE @objectid INT --Object id for table being analyzed
DECLARE @indexid INT --Index id for the target index for the STATS_DATE() function

----------------------------------------------------------------------------------
-- ******VARIABLE ASSIGNMENTS******
----------------------------------------------------------------------------------
SELECT @dbid = DB_ID(DB_NAME())
SELECT @objectid = OBJECT_ID(@table_name)

----------------------------------------------------------------------------------
-- ******Load @IndexTable with Index Metadata******
----------------------------------------------------------------------------------
INSERT INTO @IndexTable
(
[Database], [Table], [Index Name], index_id, [object_id],
[Index Type], [Alloc Unit Type], [Avg Frag %], [Row Ct]
)
SELECT
DB_NAME() AS "Database"
@table_name AS "Table"
SI.NAME AS "Index Name"
IPS.index_id, IPS.OBJECT_ID, --These fields included for joins only
IPS.index_type_desc, --Heap, Non-clustered, or Clustered
IPS.alloc_unit_type_desc, --In-row data or BLOB data
CAST(IPS.avg_fragmentation_in_percent AS decimal(5,2)),
IPS.record_count
FROM sys.dm_db_index_physical_stats (@dbid, @objectid, NULL, NULL, 'sampled') IPS
LEFT JOIN sys.sysindexes SI ON IPS.OBJECT_ID = SI.id AND IPS.index_id = SI.indid
WHERE IPS.index_id <> 0

----------------------------------------------------------------------------------
-- ******ADD STATISTICS INFORMATION******
----------------------------------------------------------------------------------
DECLARE curIndex_ID CURSOR FOR
SELECT I.index_id
FROM @IndexTable I
ORDER BY I.index_id

OPEN curIndex_ID
FETCH NEXT FROM curIndex_ID INTO @indexid

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @IndexTable
SET [Stats Update Dt] = STATS_DATE(@objectid, @indexid)
WHERE [object_id] = @objectid AND [index_id] = @indexid

FETCH NEXT FROM curIndex_ID INTO @indexid
END

CLOSE curIndex_ID
DEALLOCATE curIndex_ID

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-08-13 : 11:22:01

You are missing a few commas in that select statement.

SELECT
DB_NAME() AS "Database",
@table_name AS "Table",
SI.NAME AS "Index Name",

yslatha
Starting Member

8 Posts

Posted - 2008-08-13 : 13:25:27

thank you very much and sorry i messup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:32:21

quote:
Originally posted by mcrowley

You are missing a few commas in that select statement.
SELECT
DB_NAME() AS "Database",
@table_name AS "Table",
SI.NAME AS "Index Name",

Avoid using "",'' etc for alias names

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources