Table and Column request as variables error.

[Chris Auer]

I am trying to run a query where my Table and my request are variables
(Will be used in Stored Procedure)

Declare @Email VARCHAR(100)
Declare @Table VARCHAR(50)
Declare @Count VARCHAR(8)
DECLARE @cmd VARCHAR(500)

set @Table = 'tblManager'
set @Email = 'c****@tampabay.rr.com'

Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email
= ' + @Email

exec(@cmd)

The error I get is

Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'c****@tampabay.rr.com'.

I have worked on this for a while, any help would be greatly
appreciated.

Chris Auer

[Simon Hayes]

"Chris Auer" <ch********@gmail.com> wrote in message
news:ci********@odah37.prod.google.com...

I am trying to run a query where my Table and my request are variables
(Will be used in Stored Procedure)

Declare @Email VARCHAR(100)
Declare @Table VARCHAR(50)
Declare @Count VARCHAR(8)
DECLARE @cmd VARCHAR(500)

set @Table = 'tblManager'
set @Email = 'c****@tampabay.rr.com'

Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email
= ' + @Email

exec(@cmd)

The error I get is

Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'c****@tampabay.rr.com'.

I have worked on this for a while, any help would be greatly
appreciated.

Chris Auer

You need to put quotes around the @Email value - without quotes it's treated
as a column name:

Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email =
''' + @Email + ''''

select * from tblManager where Email = ca***@tampabay.rr.com -- doesn't work
select * from tblManager where Email = 'c****@tampabay.rr.com' -- does work

If you use dynamic SQL, it's often useful to add a @Debug parameter to your
procedure which displays dynamic SQL strings before executing them. That
way, if something isn't working as expected, you can execute the procedure
with the @Debug parameter set and see what the complete SQL string looks
like after the variables have been substituted.

Simon

[Erland Sommarskog]

Chris Auer (ch********@gmail.com) writes:

I am trying to run a query where my Table and my request are variables
(Will be used in Stored Procedure)

So what is the point with using a stored procedure then? If you are
passing tables as variables, you have a problem with your table design.

A longer discussion on this is on http://www.sommarskog.se/dynamic_sql.html.
There you can also learn to use sp_executesql, so you can avoid the
syntax-error problem you ran into.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp