Saving stored procedures 1

[vba317]

I am running SQL 2005. I have a stored procedure that I wanted to modify. When I modify it I get a message to modify an SQL procedure which I say no to, so how do I save the stored procedure?

 

[yelworcm]

Save it as a text file?

 

[SQLScholar]

What does the message say? If you havent got permission then you cant save the stored procedure.

You can save a script of a stored procedure (As per previous poster) but you cannot save the stored procedure.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[vba317]

How do I save the script then?

 

[SQLBill]

What do you mean by this? What is the message that you are saying no to?

When I modify it I get a message to modify an SQL procedure which I say no to,

It sounds like you are telling us that you modify the stored procedure and then you get a message asking if you really want to modify it and you say no. Why would you say no to modifying a stored procedure when that is what you are trying to do? Sounds like there is something missing in your post and it's making your question confusing.

Are you really trying to modify an existing stored procedure or are you trying to make a copy of a stored procedure so you can work with it (like for testing)?

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLScholar]

How do I save the script then?

In management studio - when you have the script open, go to file save as. This will then save an SQL file which contains the code to make the stored procedure.

Your terminology is quite confusing however so this may not be what you are trying to achieve. If you want more answers you need to define what you are trying to achieve and what is causing you an issue (error message, steps taken, dont know how to do X etc).

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[vba317]

I will try to be clearer. What I am talking about is if I have a stored procedure. I make a modification. I get the message to save the procedure. A message comes up say to save the procedure as Sql1. I save the procedure as Sql1. When I close that session and open studio manager again. Go to the programmability section. The procedure was not saved.

 

[gmmastros]

When you execute a "Create Procedure" query, it is saved in the database at that time. You can save the code to a file if you want, but the only way to use a stored procedure is to create it and then you can run it.

Try this:

Open SQL Server Management Studio.
Connect to your user database.
Open a new query window.
Copy/paste the following code.

Create Procedure HelloWorld
AS
If DatePart(Hour,GetDate()) < 12
    Select 'Good Morning'
Else If DatePart(Hour,GetDate()) < 5 
    Select 'Good Afternoon'
Else
    Select 'Good Evening'

Now run the code. When running it, you will not see the results of the query, but the stored procedure will be saved to the database. To run the code, press the F5 key, or click "Query" -> Execute, or click the green triangle pointing to the right.

At this point, the query is saved. Now do this...

Click the "New Query" button.
Copy/paste the following code to the window.

Exec HelloWorld

Now run this code. You will see the results of the query (Good morning, Good afternoon, or Good Evening).

If you close the SQL Server Management Studio application and then open it again, you will be able to re-run the "Exec HelloWorld" procedure again without having to do anything extra.

In SQL Server Management Studio, you probably see an "Object Explorer" window. If you don't, click the View menu item, and then Object Explorer. Expand "Databases", Expand your user database, expand programability, expand "Stored Procedures". You should see the "Hello World" stored procedure.

You can expand the stored procedure to see the parameters. You can right click on the stored procedure modify, execute, delete, rename etc....

When you are done with this example, please do not forget to delete the HelloWorld stored procedure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Nice George. You forgot the part about ALTER PROCEDURE.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[yelworcm]

After you "save" a procedure, you need to refresh the list of stored procedures. You should see your changes after that.

 

[gmmastros]

djj55,

Not everyone likes to use Alter Procedure. The reason is that the meta data for it doesn't always work the way you think it should.

If you run this...

Select * From Information_Schema.Routines

You will see a list of your stored procedures. One of the columns is "Created" and another is "LAST_ALTERED". The Last Altered column is not always correct, based on SQL version and perhaps some other things too. As a result of this, some people like to drop procedures and then create them instead of altering procedures.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

You need to alter your procedure:

http://msdn.microsoft.com/en-gb/library/ms189762.aspx

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[djj55]

The only reson I brought up alter is vba317 indicated they were modifying. The "drop" may be what vba317 needs.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!