Syntax Error when creating simple Proc

[mapat]

Hello,

How come when I run the following batch it gives me an error?

GO
CREATE PROC dbo.sp_FixIndexFragRebuild @table varchar(40), @index varchar(40)
AS
BEGIN
IF (@index is Null)
ALTER INDEX ALL ON @table REBUILD
ELSE
ALTER INDEX @index ON @table REBUILD

END
GO

Error:
Msg 102, Level 15, State 1, Procedure sp_FixIndexFragRebuild, Line 5
Incorrect syntax near '@table'.

Thank you very much

 

[MSAccessRookie]

Hello,

How come when I run the following batch it gives me an error?

GO
CREATE PROC dbo.sp_FixIndexFragRebuild @table varchar(40), @index varchar(40)
AS
BEGIN
IF (@index is Null)
ALTER INDEX ALL ON @table REBUILD
ELSE
ALTER INDEX @index ON @table REBUILD

END
GO

Error:
Msg 102, Level 15, State 1, Procedure sp_FixIndexFragRebuild, Line 5
Incorrect syntax near '@table'.

Thank you very much

Not sure if it makes any difference, but ALL of my Stored Procedures and Functions are formatted as follows:

{ CREATE/ALTER } { PROCEDURE/FUNCTION } [dbo].[sp_FixIndexFragRebuild] @table varchar(40), @index varchar(40)
​

 

[mapat]

Not sure if it makes any difference, but ALL of my Stored Procedures and Functions are formatted as follows:

{ CREATE/ALTER } { PROCEDURE/FUNCTION } [dbo].[sp_FixIndexFragRebuild] @table varchar(40), @index varchar(40)
​

I tried that as well and it doesn't work.
Thanks

 

[MSAccessRookie]

I tried that as well and it doesn't work.
Thanks

Sorry that it did not work, but have no fear, there are a number of SQL Server specialists that come by here on a regular basis, and I am sure one of them will be able to assist you.

 

[WayneRyan]

mapat,

I don't think that you can use variables to represent things in commands like:

Drop Table @TableName
Select * From @DatabaseName..@TableName

etc.

I think you have to use dynamic SQL

GO
CREATE PROC dbo.sp_FixIndexFragRebuild @table varchar(40), @index varchar(40)
AS
BEGIN
Declare @sql NVarchar(1000)
IF (@index is Null)
   Begin
      sql = 'ALTER INDEX ALL ON ' + @table + ' REBUILD'
      Exec sp_executesql @sql
   End
ELSE
   Begin
      sql = 'ALTER INDEX ' + @index + ' ON ' + @table + ' REBUILD'
      Exec sp_executesql @sql
   End
END
GO

hth,
Wayne