Erro creating procedure oneligne ok two ligne errors!!

• maloumsalem77

  Valued Member

  Points: 58

  More actions

  May 7, 2013 at 11:07 am

  #272239

  Hi Everyone,

  I got an error when I try to create this procedure, when I remove one ligne (like comment one ligne) it's ok, but when I add both of them, I got the error below.

  Please Help me.

  create procedure SALEM_DETACHE @TableName varchar(50)

  as

  Begin

  ALTER DATABASE @TableName SET SINGLE_USER WITH ROLLBACK

  EXEC master.dbo.sp_detach_db @dbname = @TableName, @skipchecks = 'false'

  end

  Msg 102, Level 15, State 1, Procedure SALEM_DETACHE, Line 6

  Syntaxe incorrecte vers '@TableName'.

  Msg 319, Level 15, State 1, Procedure SALEM_DETACHE, Line 6

  Syntaxe incorrecte près du mot clé 'with'. Si l'instruction est une expression de table commune, une clause xmlnamespaces ou une clause de contexte de suivi des modifications, l'instruction précédente doit se terminer par un point-virgule.

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  May 7, 2013 at 12:35 pm

  #1613116

  try using dynamic sql for your alter query

  declare @sql nvarchar(max)

  set @sql = 'ALTER DATABASE '+@TableName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'

  exec sp_executesql @sql;

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]