Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Modify an Existing Stored Procedure in MS SQL Server?
Submitted by: AdministratorIf you find a mistake in an existing stored procedure previously created, you can drop (delete) it and create it again correctly. But dropping a stored procedure may affect other database objects who are depending on this stored procedure.
So the best way to correct a mistake in an existing stored procedure is to use the "ALTER PROCEDURE" statement as shown in the following tutorial example:
USE GlobalGuideLineDatabase;
GO
-- Finding a mistake - the last line is wrong
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
(1 row(s) affected)
-- Modifying the stored procedure
ALTER PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
GO
Command(s) completed successfully.
Submitted by: Administrator
So the best way to correct a mistake in an existing stored procedure is to use the "ALTER PROCEDURE" statement as shown in the following tutorial example:
USE GlobalGuideLineDatabase;
GO
-- Finding a mistake - the last line is wrong
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
(1 row(s) affected)
-- Modifying the stored procedure
ALTER PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
GO
Command(s) completed successfully.
Submitted by: Administrator
-- Confirmation of the correction
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
(1 row(s) affected)
Submitted by: Administrator
SELECT m.definition
FROM sys.sql_modules m, sys.procedures p
WHERE m.object_id = p.object_id
AND p.name = 'ShowFaq';
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
PRINT 'Number of questions:';
SELECT COUNT(*) FROM Faq;
PRINT 'First 5 questions:'
SELECT TOP 5 * FROM Faq;
END;
(1 row(s) affected)
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.