I am trying to modify an existing table-valued function. When I open it, it turns into a query. So when I attempt to save it, I am prompted with a save as box for an sql statement to 'My Projects' folder, rather than saving the function as all the others listed in my Object explorer. How do I save a function as a function?
-
-
I am assuming that you are using query analyser to make the change
Saving means that, saving a copy to the file system, to save it into the database, execute it; you will see it has a Create statement at the top.
However, you will need to drop it first otherwise it will give an error - query analyser again can create the drop script for you -
Better have a saved copy of that function first. Once the query opens, copy everything, paste it to an editor (notepad is ok) and save it as any text file. (I usually use .sql as extension).
Now, find the string "create function" and replace the word "create" with the word "alter", then execute. Remember, once it's successfully executed, you will loose the copy of the function before you made any modification, hence the saving using an editor.
Good Luck!!!
~~ CKComment
-
When you select “Modify” from the menu to open the function into a query – it creates the query as an ALTER Function (“ALTER FUNCTION [dbo].[FunctionName] (@Paramters…”) all you need to do is execute the query after you have made the changes. No saving, just execute.
PS. The advise to save the old function prior to your modifications is sound.Comment
Comment