T-SQL syntax issue with 2005

• Jon Stranger (old)

  SSC Journeyman

  Points: 88

  More actions

  April 8, 2006 at 4:48 am

  #67358

  I am in the process of migrating from 2000 to 2005 and have hit what appears to be a difference in T-SQL syntax.

  The following SP uses dynamic SQL and is fine on my 2000 database:

  CREATE PROCEDURE [dbo].[DeleteWord]

  (@WordLength int, @Word nvarchar(32))

  AS

  DECLARE @err int

  DECLARE @Qry varchar(100)

  DECLARE @TableName varchar(4)

  SET @TableName = CONVERT(varchar(2),@WordLength) + 'AZ'

  SET @Qry = "DELETE FROM dbo.[" + @TableName + "] WHERE WORD = '" + @Word + "'"

  EXEC(@Qry)

  SELECT @err = @@error IF @err <> 0 RETURN @err

  But 2005 (Management Studio) objects to the SET @Qry statement with:

  Msg 207, Level 16, State 1, Procedure DeleteWord, Line 9

  Invalid column name 'DELETE FROM dbo.['.

  Msg 207, Level 16, State 1, Procedure DeleteWord, Line 9

  Invalid column name '] WHERE WORD = ''.

  Msg 207, Level 16, State 1, Procedure DeleteWord, Line 9

  Invalid column name '''.

  Anyone have any idea what the issue is or can suggest how to work round this?

  Jon Stranger

• David Scotland-132255

  SSCertifiable

  Points: 5599

  More actions

  April 8, 2006 at 8:11 am

  #631604

  If you check your database options you'll probably find you've got quoted identifiers on try this

  CREATE

  PROCEDURE [dbo].[DeleteWord]

  (

  @WordLength int, @Word nvarchar(32))

  AS

  DECLARE

  @err int

  DECLARE

  @Qry varchar(100)

  DECLARE

  @TableName varchar(4)

  SET

  @TableName = CONVERT(varchar(2),@WordLength) + 'AZ'

  SET

  @Qry = 'DELETE FROM dbo.[' + @TableName + '] WHERE WORD = '' + @Word + '''

  EXEC

  (@Qry)

  SELECT

  @err = @@error IF @err <> 0 RETURN @err

• Jon Stranger (old)

  SSC Journeyman

  Points: 88

  More actions

  April 8, 2006 at 8:52 am

  #631606

  Many thanks for such a quick and helpful response. In fact quoted identifiers were off, but the modified syntax has worked.