syntax error in 2005 - 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 2000 Forums

Transact-SQL (2000)

syntax error in 2005

Author

Topic

jstranger
Starting Member

11 Posts

Posted - 2006-04-08 : 06:37:52

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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-08 : 06:55:35

I guesss Double quotes doesnt work in SQL 2005..

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

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-04-09 : 02:09:09

I rekcon you're still a couple of single quotes short!


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

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources