[thelist] (sql2k) syntax error in sproc

[Anthony Baratta]

At 07:09 AM 7/9/2003, Tab Alleman wrote:
>I have the following code in a sproc:
>
>IF EXISTS (SELECT *
>            FROM sysobjects
>            WHERE id = object_id(N'[dw_tab].[dbo].[@ArchiveTableName]')
>            AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

Try regenerating the above line with SQL Manager, using the Generate SQL 
Scripts Option (Right Mouse Click on the Target Table and select All Tasks, 
Generate SQL Scripts, click preview on the dialog box, cut and paste the 
code out).

When I did that I got a very similar line, but my sysobjects table was 
defined more explicitly. Maybe yours needs to be too. e.g.

if exists
    (select * from dw_tab.dbo.sysobjects
     where id = object_id(N'[dw_tab].[dbo].[@ArchiveTableName]')
     and OBJECTPROPERTY(id, N'IsUserTable') = 1)

The other thing might be the N' is throwing off the @ArchiveTableName. e.g.

if exists
    (select * from dw_tab.dbo.sysobjects
     where id = object_id(dw_tab.dbo. at ArchiveTableName')
     and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Or maybe you should just explicitly change to the target DB. e.g.

use dw_tab
if exists
    (select * from dbo.sysobjects
     where id = object_id(@ArchiveTableName')
     and OBJECTPROPERTY(id, N'IsUserTable') = 1)


---
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."