column as result

Hello all,
I have this procedure
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select * from '+@.column
EXEC (@.sql)
I have this mistake:
Invalid object name '<@.column>'
Could someone tell me why?
Inatry to run these and find the result
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
and see if you have a table or view by that result name.|||Are you sure the expected dynamic statement is valid. i.e does the @.column
value pass a valid object name .
What valie is returened from this statement ?
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
I just ran the following as a test , and it worked fine .
declare @.column varchar (100)
declare @.sql varchar(200)
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
SET @.sql ='select * from ' + @.column
EXEC (@.sql)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144079096.559619.115510@.j33g2000cwa.googlegroups.com...
> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||Step through it:
/*Declare varialbes */
declare @.column varchar(200)
declare @.sql varchar(200)
/*The statement below sets the value of @.column to the (hopefully one)
value of lastname for the employee with an ID_employee value of 3 */
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
/*Suppose that the value stored in @.column is now 'williams' */
SET @.sql ='select * from '+ @.column
/* The above evaluates to 'select * from williams', and 'williams' is
probably
not an object in your database. You could use the PRINT statement to
see what you would be EXECing */
PRINT @.sql
EXEC (@.sql)
"ina" wrote:

> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||yes thank you I have the result I can see all the table but if :
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select firstname from employee where lastname='+@.column
EXEC (@.sql)
but I have this error now: Invalid column name 'pittet' (it is the last
name of an employee)
@.column needs to give me the last name of the employee ID number 3 and
sql need to give me the first name of the employee which lastname is
pittet.
ina|||SET @.sql ='select firstname from employee where lastname=''' + @.column +
''''
EXEC (@.sql)
p|||On 3 Apr 2006 09:23:44 -0700, ina wrote:

>yes thank you I have the result I can see all the table but if :
>declare @.column varchar(200)
>declare @.sql varchar(200)
>SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
>SET @.sql ='select firstname from employee where lastname='+@.column
>EXEC (@.sql)
>but I have this error now: Invalid column name 'pittet' (it is the last
>name of an employee)
>@.column needs to give me the last name of the employee ID number 3 and
>sql need to give me the first name of the employee which lastname is
>pittet.
Hi Ina,
I see that Rogas69 already posted a reply to show the error in your
code. But he or she didn't address your bigger error - why are you even
using dynamic SQL here? Why not do it in a single query?
If the requirement is to show the first name of every employee who has
the same last name as employee 3, then you could use
SELECT firstname
FROM employee
WHERE lastname = (SELECT lastname
FROM employee
WHERE ID_employee = '3')
And if that was not your requirement, then the code you posted (with the
correction posted by Rogas69) won't produce the required results.
Hugo Kornelis, SQL Server MVP|||Thank you all for these answers :)
I'd prefer to use dynamic SQL because I would like to understand how to
declare variable in a sql code; with your help I could understand more.
:)|||On 3 Apr 2006 23:57:03 -0700, ina wrote:

>Thank you all for these answers :)
>I'd prefer to use dynamic SQL because I would like to understand how to
>declare variable in a sql code; with your help I could understand more.
Hi Ina,
Okay. Just make sure that you read (and understand) everything on this
page: http://www.sommarskog.se/dynamic_sql.html.
Hugo Kornelis, SQL Server MVP