SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Regarding stored procedure


Regarding stored procedure

Author
Message
boobalanmca07
boobalanmca07
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 14
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
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3725 Visits: 6512
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/
boobalanmca07
boobalanmca07
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 14
without remove alter statement , how to solve the problem.
because two sql statement in one stored procedure,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89281 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5104 Visits: 5478
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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28726 Visits: 39977
Eugene Elutin (11/27/2012)
[quote]
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search