Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
syedripon
Starting Member
9 Posts |
 Posted - 2005-09-29 : 23:29:06
|
| Hi:I was working in SQL MS Enterprise Manager. I opened my DB and then tried to write a new Stored procedure(Stroed Procedure-->right click-->new procedure--> blank SP window). I copied some .sql code from VB. It had a beggining line like:" If exists in Sytemobjects(newprocedure)drop procedure newprocedureCreate proc newprocedureas "----Now when I am copying this code in Enter Manager, the If part just vanishes and the code starts(opening from Enter Manager) fromCreate proc newprocedure.What is the catch here. I tried to see all other SPs in my DB and none of them has the If part.What is the wrong. Why it does not take the If part. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-30 : 00:23:19
|
| I reckon if you are doing it from E.M. it is only interested in the actual code for the SProc, not any other control code. In fact for an existing SProc the code it offers you is ALTER - this will a) not DROP the existing Sproc if there is a syntax error in your code and B) preserve any permissions. So once your SProc is there you'll be better off with that style of working.However, I don't use EM for that job. I user Query Analyser, we keep each SProc in a separate file on disk, that enables us to manage rollout to Dev, QA and then Production, you can use Version Management, and put any other logic in the file too - such as the conditional DROP and (in our case) the GRANT permissions statements etc tooKristen |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-03 : 21:58:20
|
| no catch...your vb script was designed to check for existence of the sp before creating it while in EM, it doesn't show you the mechanism on checking, but it checks for existence is you try to save the spif you will also notice, after the if part, you have a GO which means you have two transactions, one for IF and the other for CREATE, in EM sp, you can't do this because each sp is considered as one transaction and it is a violation to specify otherwise--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Stroed Procedure code