Dynamic SQL - Invalid object name error

Question

I am using dynamic SQL on SQL Server 2008 to select specified columns from a row but I keep getting the following error:

Invalid object name 'Form'

My code is as follows:

DECLARE @SQL varchar(MAX)

SET @SQL = 'select 
[City],[Place]'
+
'
from Form where [Age:] =  20'

EXEC (@SQL)

I also tried using + QUOTENAME(@Table) and declared@Table as nvarchar(MAX) but could not define that @Table is basically the Form table in my database.

As I checked the previous examples, people were able to select columns from tables the same way without getting errors, so what could be the reason for the error I get? Should I use @QUOTENAME function at all?

Help will be appreciated.

First of all, I am sure I do have a table that is called Form because I can do basic operations on it. And I have also removed @Table declaration because I could not set @Table to my Form table in database. — KontrCode
– KontrCode, Commented Mar 15, 2018 at 6:47
do a PRINT @SQL, copy the result and paste into a new query window and execute. Any error ? — Squirrel
– Squirrel, Commented Mar 15, 2018 at 7:01

Raj · Accepted Answer · 2018-03-15 06:54:14Z

3

Try below query and if you still get error check basic things first.

DECLARE @SQL varchar(MAX)
SET @SQL = 'select [City],[Place] from [Form] where [Age] =  20'
EXEC (@SQL)

Is table [Form] present in database ?
Are you running in correct DB?
I see ":" after AGE is that in col name ?
If Age is varchar try to add "'" before and after 20.

answered Mar 15, 2018 at 6:54

Raj

5125 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KontrCode Over a year ago

It is solved sir, thank you. I should have checked the syntax better since there is no correction system in Dynamic Programming because we write queries in quotation marks. The database I have imported was called 'Form', not Form (as also @Nisarg has warned me) . The rest of query was written true.

Zorkolot · Accepted Answer · 2018-03-15 06:56:22Z

1

You describe @table. Table is a reserved keyword, so it's a good practice not to use that word. Lets assume @mytable is a variable containing the table name for the query. In that case, you concatenate like you do with the rest of the string. You also need to be connected to the database you are trying to query from.

For example:

DECLARE @SQL varchar(MAX)
DECLARE @mytable varchar(1000) = 'Form' --put the table name here

SET @SQL = 'select [City], [Place] '
         + 'from ' + @mytable + ' where [Age:] =  20'

EXEC (@SQL)

answered Mar 15, 2018 at 6:56

Zorkolot

2,0271 gold badge12 silver badges8 bronze badges

2 Comments

Squirrel Over a year ago

even though table is a reserved word, there is nothing wrong using variable @table at all

Zorkolot Over a year ago

True. But if you declare a variable in this manner it will highlight @table with the reserved keyword color in stackoverflow. Also, you can use reserved keywords with brackets... example CREATE [TABLE] TABLE (...), which is valid- but any DBA is going to tell you not to do it.

Collectives™ on Stack Overflow

Dynamic SQL - Invalid object name error

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related