Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Regarding stored procedure Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 8:00 AM
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

Post #1389161
Posted Tuesday, November 27, 2012 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #1389172
Posted Tuesday, November 27, 2012 7:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 8:00 AM
Points: 13, Visits: 14
without remove alter statement , how to solve the problem.
because two sql statement in one stored procedure,
Post #1389177
Posted Tuesday, November 27, 2012 7:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1389192
Posted Tuesday, November 27, 2012 7:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1389208
Posted Tuesday, November 27, 2012 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1389212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse