Altering stored procedure from another procedure
up vote
0
down vote
favorite
I would like to know how to alter a stored procedure from another stored procedure. I have created one stored procedure called dbo.change, and in this procedure I would like to alter other stored procedure that needs to be altered.
But currently I use alter procedure and when the change procedure is compiling it fails at the alter. Is there a method of doing this?
sql
sql-server sql-server-2008 stored-procedures edited Sep 9 '14 at 12:02
marc_s
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
add a comment |
up vote
0
down vote
favorite
I would like to know how to alter a stored procedure from another stored procedure. I have created one stored procedure called dbo.change, and in this procedure I would like to alter other stored procedure that needs to be altered.
But currently I use alter procedure and when the change procedure is compiling it fails at the alter. Is there a method of doing this?
sql
sql-server sql-server-2008 stored-procedures edited Sep 9 '14 at 12:02
marc_s
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
7
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
1
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I would like to know how to alter a stored procedure from another stored procedure. I have created one stored procedure called dbo.change, and in this procedure I would like to alter other stored procedure that needs to be altered.
But currently I use alter procedure and when the change procedure is compiling it fails at the alter. Is there a method of doing this?
sql
sql-server sql-server-2008 stored-procedures edited Sep 9 '14 at 12:02
marc_s
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
I would like to know how to alter a stored procedure from another stored procedure. I have created one stored procedure called dbo.change, and in this procedure I would like to alter other stored procedure that needs to be altered.
But currently I use alter procedure and when the change procedure is compiling it fails at the alter. Is there a method of doing this?
sql
sql-server sql-server-2008 stored-procedures sql
sql-server sql-server-2008 stored-procedures edited Sep 9 '14 at 12:02
marc_s
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
edited Sep 9 '14 at 12:02
marc_s
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
edited Sep 9 '14 at 12:02
marc_s
568k12810991249
edited Sep 9 '14 at 12:02
marc_s
568k12810991249
edited Sep 9 '14 at 12:02
marc_s
568k12810991249
568k12810991249
asked Sep 9 '14 at 11:51
user3086751
147116
asked Sep 9 '14 at 11:51
user3086751
147116
asked Sep 9 '14 at 11:51
user3086751
147116
147116
7
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
1
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32
add a comment |
7
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
1
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32
7
7
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
1
1
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32
add a comment |
1 Answer
1
active
oldest
votes
up vote
4
down vote
accepted
You need to use dynamic SQL to alter a stored procedure from within another. For example:
ALTER PROC dbo.Change
AS
EXEC sp_executesql N'ALTER PROC dbo.SomeOtherProc AS ...';
GO
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f25743960%2faltering-stored-procedure-from-another-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
You need to use dynamic SQL to alter a stored procedure from within another. For example:
ALTER PROC dbo.Change
AS
EXEC sp_executesql N'ALTER PROC dbo.SomeOtherProc AS ...';
GO
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
add a comment |
up vote
4
down vote
accepted
You need to use dynamic SQL to alter a stored procedure from within another. For example:
ALTER PROC dbo.Change
AS
EXEC sp_executesql N'ALTER PROC dbo.SomeOtherProc AS ...';
GO
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
add a comment |
up vote
4
down vote
accepted
up vote
4
down vote
accepted
You need to use dynamic SQL to alter a stored procedure from within another. For example:
ALTER PROC dbo.Change
AS
EXEC sp_executesql N'ALTER PROC dbo.SomeOtherProc AS ...';
GO
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
You need to use dynamic SQL to alter a stored procedure from within another. For example:
ALTER PROC dbo.Change
AS
EXEC sp_executesql N'ALTER PROC dbo.SomeOtherProc AS ...';
GO
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
answered Sep 9 '14 at 11:56
Dan Guzman
22.5k31540
22.5k31540
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f25743960%2faltering-stored-procedure-from-another-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
7
why would you need to that?
– Mitch Wheat
Sep 9 '14 at 11:53
1
I do not know why you would want to do that, but you could use a dynamic sql.
– Giannis Paraskevopoulos
Sep 9 '14 at 11:54
It might be possible using dynamic sql, but why would you want to do this? What is the real problem you are trying to solve?
– jpw
Sep 9 '14 at 11:54
because i need to make changes to a live database, and am testing it on a local one to see changes to the tables don't affect the data, and these procedure have local data-type declared need to change these so they match the new table schema
– user3086751
Sep 9 '14 at 11:55
@user3086751 the real question is the one behind your 'because i need to make changes to a live database'. which changes you need to make? why they do have to be performed by a stored procedure? imho the solution you are trying to implement is a paved highway to major troubles: executing alter statements from stored procedures will produce naive behaviour almost impossible to trace & debug.
– Paolo
Sep 9 '14 at 13:32