November 27, 2012 at 6:49 am
alter proc procstudent1
@proctamil int
as
begin
update student
set tamil=@proctamil
where studname='mani'
alter table student
add place varchar(30)
end
execute query:
exec procstudent1 @proctamil=57
error:
Msg 2705, Level 16, State 4, Procedure procstudent1, Line 8
Column names in each table must be unique. Column name 'place' in table 'student' is specified more than once.
so how to avoid this error
November 27, 2012 at 6:59 am
You need to remove the alter table statement from the procedure. With it, the procedure can work only the first time that it runs. After the column was added to the table, if you'll try to add a new column that has a name that another column already has, you'll get this error.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 27, 2012 at 7:09 am
without remove alter statement , how to solve the problem.
because two sql statement in one stored procedure,
November 27, 2012 at 7:29 am
No, the problem is not because there are two statements. It's because if you run that more than once, the second, third, etc executions will fail because the column already exists. You cannot add the same column to a table multiple times and if you try to run that procedure more than once, that's exactly what you're trying to do. Hence it fails.
Remove the Alter. DDL typically has no place in a procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2012 at 7:53 am
boobalanmca07 (11/27/2012)
without remove alter statement , how to solve the problem.because two sql statement in one stored procedure,
It's a very bad idea of making such stored procedure!
You should not mix one-off schema alteration with common data maintenance.
Schema alterations should be performed as a separate database change exercise!
No DBA (in any serious organisation) would allow your proc to exist.
To "fix" this you need to completely remove ALTER TABLE out of this proc and execute it once in a controlled change-release manner.
Technically speaking, it is possible to leave both statements in, however I'm refusing to show you how it can be done, as it would constitute really very bad design and code.
You may find some less perfectionists around who will not refuse to help you with this...
November 27, 2012 at 7:57 am
Eugene Elutin (11/27/2012)
You may find some less perfectionists around who will not refuse to help you with this...
ouch! i resemble that remark!
anyway, even with the caveats we mentioned, and how bad it is, i doubt the OP is planning on listening anyway.
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply