Hello,
I am trying to drop a column from a table with a stored procedure that
chacks if the column exists before droping it. However, I am having trouble
with passing the Table Name and Column Name to the ALTER TABLE command. The
code is:
CREATE PROCEDURE usp_DeleteColumnEx
@.TableName varchar(200),
@.ColumnName varchar(200)
AS
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')= 1
AND O.Name = @.TableName
AND C.Name = @.ColumnName)
ALTER TABLE @.TableName DROP COLUMN @.ColumnName
When I try to execute this the server returns an error: "Incorrect syntax
near '@.TableName'". I am assuming that I cannot just pass the table name as
a
parameter. Is there a another way to do this?
Thank you for your help.
Daniel> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
You can't do this - SQL Server has to know what objects you're talking
about. The best you could do is dynamic SQL, please read:
http://www.sommarskog.se/dynamic_sql.html
On a side note, why on earth are you changing your table structure on the
fly like this? Sounds very dangerous and suspicious, but not in the Austin
Powers way.|||"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9A9DCE9F-191A-4E76-AD5A-FC6E7A316E61@.microsoft.com...
> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having
> trouble
> with passing the Table Name and Column Name to the ALTER TABLE command.
> The
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name
> as a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel
Why would you want a proc that drops columns? Help me understand what you
are trying to achieve.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Aaron and David,
Thank you for your replies. The reason I need to alter the table dynamically
is
that I have a table where each record is corresponds to one employee, each
column represents a different skill involved in the employees' everyday work
.
In each column every employee have assigned a number corresponding to their
skill level. According to their skills they are assigned to their work
stations, which is VERY important for the management.
This is a basically how the table looks like:
Create table SkillsCheck (
emp_id int,
skill1 int,
skill2 int,
skill3 int,
skill4 int)
The problem is that the company has the option to change what skills they
want to track, so when that happens I have to drop or add a column to the
table to reflect the current situation. For example, they might decide that
they do not want to track skill3 anymore, but want to track skill5, so my
program needs to drop skill3 column and add skill5 column. I am working with
VB.NET and I can execute the ALTER TABLE statement from my code, but I think
that it would be better to use stored procedure that check if the column
exists before adding or droping.
After considering the situation I decided that would be easier to alter the
table instead of keeping the information in multiple tables. If you have
encountered a similiar scenario maybe you can give me an advice for more
efficient approach to this problem.
Thank you for your help,
Daniel
"Daniel" wrote:
> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having troubl
e
> with passing the Table Name and Column Name to the ALTER TABLE command. Th
e
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name a
s a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel|||Daniel wrote:
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table dynamical
ly
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday wo
rk.
> In each column every employee have assigned a number corresponding to thei
r
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide tha
t
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working wi
th
> VB.NET and I can execute the ALTER TABLE statement from my code, but I thi
nk
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter th
e
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>
The type of relationship between employees and skills is called
many-to-many. The textbook solution looks like this:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
PRIMARY KEY (emp_id,skill_code));
This has huge advantages over the design that you proposed: It can
support any number of skills. No redundancy. No nulls required. Joins
and queries always reference just one skills column. The table
structure doesn't ever need to change (!).
I recommend you read up and study some relational design theory. Most
database architects would consider your suggestion as a serious design
flaw. To appreciate why you need to understand principles like
normalization and the normal forms, which are some of the tools we use
to design effective databases.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||As you stated, it would take a schema changed to accommodate future
requirements. Such as that, you're better off by creating a skills table to
store the skills. Any changes will just be a simple delete from the tables.
Also, this approach will not incur a table lock like you have when doing
schema update.
e.g.
create table skills(skillid int primary key, skillname sysname)
create table skillscheck(empid int primary key, skillid int foreign key
references skills(skillid))
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table
> dynamically
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday
> work.
> In each column every employee have assigned a number corresponding to
> their
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide
> that
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working
> with
> VB.NET and I can execute the ALTER TABLE statement from my code, but I
> think
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter
> the
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>|||Correction. Don't forget to add a column for the skill level:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
skill_level INTEGER NOT NULL
CHECK (skill_level BETWEEN 0 AND 10),
PRIMARY KEY (emp_id,skill_code));
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I will follow your advice and will read about many-to-many relationships.
After reading more on Dynamic SQL, looks like it is not the best choice.
Thanks again,
Daniel
"David Portas" wrote:
> Correction. Don't forget to add a column for the skill level:
> CREATE TABLE EmployeeSkills
> (emp_id INTEGER NOT NULL
> REFERENCES Employees (emp_id),
> skill_code INTEGER NOT NULL
> REFERENCES Skills (skill_code),
> skill_level INTEGER NOT NULL
> CHECK (skill_level BETWEEN 0 AND 10),
> PRIMARY KEY (emp_id,skill_code));
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||OJ,
Thanks you your reply. Looks like I need to revise the database design and
reorganize the tables differently.
Best Regards,
Daniel
"oj" wrote:
> As you stated, it would take a schema changed to accommodate future
> requirements. Such as that, you're better off by creating a skills table t
o
> store the skills. Any changes will just be a simple delete from the tables
.
> Also, this approach will not incur a table lock like you have when doing
> schema update.
> e.g.
> create table skills(skillid int primary key, skillname sysname)
> create table skillscheck(empid int primary key, skillid int foreign key
> references skills(skillid))
>
> --
> -oj
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
>
>
|||
christianmala: