| Author |
Topic |
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
 Posted - 2007-01-22 : 22:15:39
|
| hii have a stored procedures that executes around 100 lines of t-sql statements. It involved creating multiple tables and also creating views base on the created tables and some atleration to the created tables like adding some new columns and update it with a default value before creating views. This is a simple data cleansing procedures.However, i am stuck with the issue of GO in procedures. I have did some research on using GO and i am convinced but have not go any clue on how can i do a workaround. Anyhelp is greatly appreciated. thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-22 : 22:58:27
|
| GO signals the end of a batch. must remove them from stored procs. can try seperating statements with semicolons if u must have seperator. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-22 : 23:00:55
|
| Actually, I bet that SP can be cleaned up a lot. why alter a table u just created to populate with a default value. why not just put default constraint on at create time?also, should be able to execute in one large batch -- just remove the GOs.make sure that all of the DDL is before any of the DML in SP for better performance |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-01-22 : 23:23:38
|
| so sorry if i have left out some info. what i meant was using select into statement for creating tables.For example, select cola, colb,colc into tbla from tblb where dateVal >= ? and dateVal <= ?.It is used for analysis purpose by using pivot tables and there are many redundant columns in the tblB and this si the reasons i need to add some columns for used in excel pivot tables.Would it still be able to execute in one large batch and make sure that DDL must come before any DML? Thanks |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-22 : 23:30:58
|
"Would it still be able to execute in one large batch and make sure that DDL must come before any DML?"you can always check for existance of tables by using referencing INFORMATION_SCHEMA.TABLE or sysobjects
KH |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-01-23 : 01:28:00
|
| thanks, i'll try later. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-23 : 08:57:35
|
| Yikes. Yet another classic class of a horrible database design making simple things many times more complicated than they need to be. Good luck. When will people learn?- Jeff |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-23 : 08:59:54
|
bad day at work jeff?  Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-23 : 09:16:51
|
1. Don't create tables using SELECT...INTO syntax unless you have compelling reason to do so2. Even in case of SELECT...INTO you can add dummy columns while creating table itself. Select Col1, Col2, 0 as Col3, '' as Col4 Into #someTable From SourceTbl Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
|
Any workaround for multiple GO in stored procedure