Invalid Object Name - SQL server 2005

when executing the following stored procedure I get Invalid Object Name dbo.Approved. The object dbo.Approved does exist, so presumably this is something to do with the way i pass the table name in as the parameter?

I should also add that i get the error either by executing the procedure via .NET, or from within SMSS.

 @tableName as nvarchar(100)
AS
 BEGIN

EXEC('

UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + '[' + @tableName +  '] As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
')

END

Edit after receiving help开发者_运维技巧 from Joe and JNK the sproc is now this but i get the error

Msg 102, Level 15, State 1, Procedure sp_Updater, Line 14  

Incorrect syntax near 'QUOTENAME'.

new sproc

@tableName as nvarchar(100),
@schemaName as nvarchar(20)
AS
BEGIN  



EXEC('
--Update NPT
UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + ' As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
')

END

With the square brackets in you string, your table reference turns into [dbo.Approved] which is not valid. The reference should be [dbo].[Approved] instead.

You might want to consider passing schema name and table name as two separate parameters.

It would also be better to use the QUOTENAME function instead of hard coding the square brackets.

declare @sql nvarchar(1000)

set @sql = N'UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
'

EXEC (@sql)

If you use brackets for the three-part-name, you need to have brackets around each section but not the period, i.e.:

[dbo].[Approved]

If you pass dbo.Approved as your parameter, your Dynamic SQL is reading it as [dbo.Approved] which would only work if you had a table named that (i.e. the dbo. is part of the table name not the schema).

Change it to:

'...[dbo].[' + @tablename + ']...

And just pass Approved as the parameter.

Your wrapping the id too early so '[' + @tableName + '] is getting translated to [dbo.approved] when it should be [dbo].[Approved]

Table names and column names are actually sysname (which is, as I recall an NVARCHAR(128) or NVARCHAR(256) - off the top of my head I don't quite remember)

Also, You are vulnerable to a SQL Injection Attack. You should validate that @tableName is a real table by checking it against INFORMATION_SCHEMA.TABLES

Finally, just to be absolutely sure, in case the real table has some odd characters in it, you should use QUOTENAME(@tableName) to fully escape the table name.