Invalid Column Name

When I execute the following command under SQL Server 2000, "ALT_1_UNIT" is
correctly interpreted as the column name ALT_1_UNIT:
set quoted_identifier on
select "ALT_1_UNIT"
from IM_ITEM
However, I would like to be able to dynamically set the column name like
this:
set quoted_identifier on
declare @.UNIT varchar(1)
set @.UNIT = '1'
select "ALT_" + @.UNIT + "_UNIT"
from IM_ITEM
When I execute this command, I get the errors
Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'ALT_'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name '_UNIT'.
From this, it appears that I cannot concatenate a column identifier and a
string.
I then tried replacing the single quotes in
set @.UNIT = '1'
with double quotes as in
set @.UNIT = "1"
That produced an additional error:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name '2'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'ALT_'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name '_UNIT'.
From this, it appears that I cannot store a column identifier in a variable.
How can I accomplish what I want to do?Paul,
I'm not sure what exactly you are trying to accomplish, however for what you
are attempting this should work fine in a stored procedure for sql 2k
CREATE PROCEDURE sp_testthis
(
@.Unit varchar(1)
)
AS
select "ALT_" + @.Unit + "_UNIT" as mystring
from
IM_ITEM
GO
mystring is what you will be selecting in code...for example (c#):
// ****************************************
**************
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=myserver;Initial
Catalog='mycatalogue';Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("sp_testthis", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1= new SqlParameter("@.Unit", SqlDbType.VarChar,
1);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
string mytest = String.Empty;
conn.Open();
SqlDataReader result =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
result.Read();
mytest = (string)result["mystring"];
result.Close();
conn.Close();
cmd.Dispose();
Label1.Text = mytest.ToString();
}
// ****************************************
**************
HTH
"Paul D." <pablodados@.hotmail.com> wrote in message
news:XuAXf.71026$9I5.5222@.tornado.ohiordc.rr.com...
> When I execute the following command under SQL Server 2000, "ALT_1_UNIT"
> is
> correctly interpreted as the column name ALT_1_UNIT:
> set quoted_identifier on
> select "ALT_1_UNIT"
> from IM_ITEM
> However, I would like to be able to dynamically set the column name like
> this:
> set quoted_identifier on
> declare @.UNIT varchar(1)
> set @.UNIT = '1'
> select "ALT_" + @.UNIT + "_UNIT"
> from IM_ITEM
> When I execute this command, I get the errors
> Server: Msg 207, Level 16, State 3, Line 4
> Invalid column name 'ALT_'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name '_UNIT'.
> From this, it appears that I cannot concatenate a column identifier and a
> string.
> I then tried replacing the single quotes in
> set @.UNIT = '1'
> with double quotes as in
> set @.UNIT = "1"
> That produced an additional error:
> Server: Msg 207, Level 16, State 3, Line 3
> Invalid column name '2'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name 'ALT_'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name '_UNIT'.
> From this, it appears that I cannot store a column identifier in a
> variable.
> How can I accomplish what I want to do?
>
>|||found a problem, this should work better
CREATE PROCEDURE sp_testthis
(
@.Unit varchar(1)
)
AS
declare @.mystring nvarchar(50)
select @.mystring = '
select ALT_' + @.Unit + '_UNIT
from
IM_ITEM'
execute(@.mystring)
GO
you will need to have select permission granted to your user on that table

> // ****************************************
**************
> protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=myserver;Initial
Catalog='mycatalogue';Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("sp_testthis", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = new SqlParameter("@.Unit", SqlDbType.VarChar,
1);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
string mytest = String.Empty;
try
{
conn.Open();
SqlDataReader result =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
result.Read();
mytest = (string)result["Alt_" + TextBox1.Text + "_UNIT"];
result.Close();
conn.Close();
cmd.Dispose();
Label1.Text = mytest.ToString();
}
catch { Label1.Text = "error message"; }
}
> // ****************************************
**************
>
sorry, hope this works better|||That will work for me. Thanks!
Paul
"alice" <alice@.nospam.com> wrote in message
news:%234%233AtcVGHA.424@.TK2MSFTNGP12.phx.gbl...
> found a problem, this should work better
> CREATE PROCEDURE sp_testthis
> (
> @.Unit varchar(1)
> )
> AS
> declare @.mystring nvarchar(50)
> select @.mystring = '
> select ALT_' + @.Unit + '_UNIT
> from
> IM_ITEM'
> execute(@.mystring)
> GO
> you will need to have select permission granted to your user on that table
>
> {
> SqlConnection conn = new SqlConnection("server=myserver;Initial
> Catalog='mycatalogue';Integrated Security=SSPI");
> SqlCommand cmd = new SqlCommand("sp_testthis", conn);
> cmd.CommandType = CommandType.StoredProcedure;
> SqlParameter param1 = new SqlParameter("@.Unit", SqlDbType.VarChar,
> 1);
> param1.Value = TextBox1.Text;
> cmd.Parameters.Add(param1);
> string mytest = String.Empty;
> try
> {
> conn.Open();
> SqlDataReader result =
> cmd.ExecuteReader(CommandBehavior.CloseConnection);
> result.Read();
> mytest = (string)result["Alt_" + TextBox1.Text + "_UNIT"];
> result.Close();
> conn.Close();
> cmd.Dispose();
> Label1.Text = mytest.ToString();
> }
> catch { Label1.Text = "error message"; }
> }
> sorry, hope this works better
>|||Paul D. (pablodados@.hotmail.com) writes:
> When I execute the following command under SQL Server 2000, "ALT_1_UNIT"
> is correctly interpreted as the column name ALT_1_UNIT:
> set quoted_identifier on
> select "ALT_1_UNIT"
> from IM_ITEM
> However, I would like to be able to dynamically set the column name like
> this:
> set quoted_identifier on
> declare @.UNIT varchar(1)
> set @.UNIT = '1'
> select "ALT_" + @.UNIT + "_UNIT"
> from IM_ITEM
Sound like you have bad database design. You will need to use dynamic
SQL, which has a lot of consequences. I have an article on my web site
on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. Of particular
interest to you is http://www.sommarskog.se/dynamic_sql.html#Dyn_update.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9799657D8C3FYazorman@.127.0.0.1...
> Paul D. (pablodados@.hotmail.com) writes:
> Sound like you have bad database design. You will need to use dynamic
> SQL, which has a lot of consequences. I have an article on my web site
> on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. Of particular
> interest to you is http://www.sommarskog.se/dynamic_sql.html#Dyn_update.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Thanks. I see your point about the database design being bad. This IM_ITEM
table includes columns ALT_1_UNIT through ALT_5_UNIT and those could be put
into a separate table. Unfortunately, I have no control over the design of
this particular database.
I do also see that I could use a case statement to determine the name of the
column based on the value of @.UNIT. Is there an advantage to doing it that
way as opposed to forming the column name through concatenation?
Paul D.|||Paul D. (pablodados@.hotmail.com) writes:
> I do also see that I could use a case statement to determine the name of
> the column based on the value of @.UNIT. Is there an advantage to doing
> it that way as opposed to forming the column name through
> concatenation?
Instead of getting five plans in the cache, you get one. Furthermore,
if you use dynamic SQL, but you neglect to prefix the table name with
"dbo.", each user will get his own plan. These are issue that you
avoid if you use stored procedures with static SQL.
And of course, if the users do not have SELECT permissions on the table,
there is a very important difference.
Overall, it's better to use a solution with static SQL when this is
possible, as it reduces complexity and thus reduces maintenance costs.
There are some exceptions to this rule, the prime example is dynamic
search conditions. Had you had 550 such columns, I might have advocated
a solution with dynamic SQL, but for five columns, no. Use CASE instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx