There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught yawning last time). Here is the question:
Question: “How do you create stored procedure? and What are the advantages of the stored procedure?”
Answer: Well, as I said, I find this question are age old, so if you search on the internet you will find many different answers. Just make sure you read the answers which are from the latest version of SQL Server as stored procedure have changed a bit since the inception of the same.
Here are few of the advantages of the stored procedure:
- Execution plan retention and reuse
- Query auto-parameterization
- Encapsulation of business rules and policies
- Application modularization
- Sharing of application logic between applications
- Access to database objects that is both secure and uniform
- Consistent, safe data modification
- Network bandwidth conservation
- Support for automatic execution at system start-up
- Enhanced hardware and software capabilities
- Improved security
- Reduced development cost and increased reliability
- Centralized security, administration, and maintenance for common routines
You can read the original article over here.
If you want to create stored procedure you can create with following simple script:
CREATE PROCEDURE MyFirstSP
AS
SELECT GETDATE();
GO
You can execute above stored procedure by following a script:
EXEC MyFirstSP
GO
The above stored procedure will return results as a current date time.
 
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
