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 |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2008-06-19 : 14:28:14
|
| Hi,Recently, I started to move more databases from SQL Server 2000 to 2005, well the Stored Procedures in 2005 is hard to handle.First of all, I created the new SP in Object Explorer, the "New Stored Procedure" query just created a new name under the Stored Procedures folder, then I have to re-open it to put in TSQL code. After I put in the code in the new SP, if I click "Save", the File Manager comes out to let me to save in a physical location (C: or D: or Network place...). But after I close and open the new SP, the code I put in there is not really in the new SP. I know that I have to "Execute" the SP in order to save the change. But the problem is I don't want to run the code at this step, the SP is to get a lot of data transactions, I'll setup a "Job" to do this. So, the question is: Is this the way to create and save a new SP? Is there any other way to save the code changes in the SP WITHOUT "Execute" it? Please give me any advise, article or links to read.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 14:40:24
|
| You don't use execute to create the stored procedure. You use CREATE PROC or CREATE PROCEDURE. Just do it in a query window and not through the new stored procedure feature. When you want to save the code, just click the save button from the query window when you've got your code displayed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-19 : 14:56:51
|
| Thank you tkizer,As I tried, "click the save button from the query window" will bring out the File Manager to save to a C: or D: or..., Yes I saved it as the same location with same name and click "Yes" to replace the old file, but the changed code is not saved. After I re-open the SP, the changes were gone, the only way to save it as I know is to click the "Execute" button. That's why I post this topic. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 15:16:52
|
| You seem to be confused what the execute button does. It does not execute the stored procedure when you have CREATE PROC/ALTER PROC. It just "saves" the code to the database. To execute a stored procedure, you use EXEC storedProc and then click execute.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-06-19 : 15:30:35
|
| That's correct. Create proc will obly create SP on the DB, it won't need to run on the DB.ManojMCP, MCTS |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-19 : 18:31:58
|
| It does confuse me, the Save button and Execute button are all listed there; The Execute button has a "!" at the left; And if you open a table the "!" Execute SQL is at the top, same as the "!" in Visual Studio; Also if you right click the SP name in the Object Explorer, you'll see the Execute Stored procedurer... option listed there. So, one word "Execute", is used for many functions. I don't know if the SQL Server 2008 has the seme way to use the "Execute"Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 16:51:31
|
| Yes still same. |
|
|
|
|
|
|
|
|
Stored Procedure is hard to Create/Save