CODEWITHSUNDEEP
sql-serverstored-procedures
MrBliz
MrBliz

Reputation: 5908

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

Upvotes: 2

Views: 5052

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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)

Upvotes: 5

JNK
JNK

Reputation: 65157

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.

Upvotes: 4

Colin Mackay
Colin Mackay

Reputation: 19175

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.

Upvotes: 1

cmsjr
cmsjr

Reputation: 59185

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

Upvotes: 3

Related Questions