1

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.

KontrCode
  • 89
  • 11
  • 1
    You describe @table? Where is this in your query? – Zorkolot Mar 15 '18 at 06:39
  • 1
    what is `Form` ? is this your table name ? – Squirrel Mar 15 '18 at 06:41
  • 1
    Did you check if you have a table by name `Form`? – Nisarg Shah Mar 15 '18 at 06:41
  • 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 Mar 15 '18 at 06:47
  • @Nisarg How can I check if executing user have permissions on the table? Is there any other permissions to specify in order select from table in Dynamic Programming? I am basically able to do any of the operations I want to do on every database on my SQL Server. – KontrCode Mar 15 '18 at 06:53
  • 1
    do a `PRINT @SQL`, copy the result and paste into a new query window and execute. Any error ? – Squirrel Mar 15 '18 at 07:01

2 Answers2

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)
  1. Is table [Form] present in database ?
  2. Are you running in correct DB?
  3. I see ":" after AGE is that in col name ?
  4. If Age is varchar try to add "'" before and after 20.
Raj
  • 462
  • 3
  • 15
  • 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. – KontrCode Mar 15 '18 at 07:49
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)
Zorkolot
  • 1,899
  • 1
  • 11
  • 8
  • even though `table` is a reserved word, there is nothing wrong using variable `@table` at all – Squirrel Mar 15 '18 at 06:59
  • 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. – Zorkolot Mar 15 '18 at 07:39