SQL mein Stored Procedure ek precompiled set of SQL queries hota hai jo ek specific task ko perform karta hai. Ye ek function ki tarah hota hai jo SQL server par store kiya jata hai aur aap ise baar-baar call kar sakte hain. Stored procedures ko aap database operations ko modular aur reusable banane ke liye use kar sakte hain.

Stored Procedure ke Features:

1. Reusability: Aap ek baar stored procedure likh kar use baar-baar call kar sakte hain bina har baar query likhne ke.
2. Performance: Ye precompiled hota hai, isliye SQL queries ko execute karne mein time kam lagta hai.
3. Maintainability: Agar koi query change karni ho, toh aapko sirf stored procedure mein change karna hota hai, query mein nahi.
4. Security: Aap stored procedure ko execute karne ka permission de sakte hain bina kisi ko direct table access diye.
5. Modularization: Complex tasks ko chhote chhote procedures mein break karke code ko modular banaya ja sakta hai.

Stored Procedure ka Syntax:

Stored procedure ko create karte waqt CREATE PROCEDURE ka use hota hai. Basic syntax kuch is tarah hota hai:

Example:

Maan lijiye aapko ek stored procedure banana hai jo employee ke details ko fetch kare based on employee ID:

Is stored procedure mein:

• GetEmployeeDetails procedure ka naam hai.
• @EmployeeID ek parameter hai, jo user input ke roop mein diya jayega.
• SELECT * FROM Employees query hai jo employee ke details ko fetch karegi.

Stored Procedure ko Execute Karna:

Stored procedure ko execute karne ke liye EXEC ya EXECUTE ka use hota hai. Example:

Ya:

Stored Procedure mein Parameters:

Stored procedure mein input (IN), output (OUT), aur input/output (INOUT) parameters ho sakte hain.

1. Input Parameter: User se input liya jata hai aur query mein use hota hai.
2. Output Parameter: Procedure execute hone ke baad result ko return karta hai.
3. Input/Output Parameter: Ye dono ka combination hota hai, jisme input bhi hota hai aur result bhi return hota hai.

Example with parameters:

Is example mein, @Salary ek output parameter hai jo employee ke salary ko store karega.

Stored Procedure ko Modify aur Drop Karna:

• ALTER PROCEDURE: Agar stored procedure mein kuch modification karna ho, toh ALTER ka use kiya jata hai.

•     ALTER PROCEDURE GetEmployeeDetails

          @EmployeeID INT

      AS

      BEGIN

          SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;

      END;

• DROP PROCEDURE: Agar aap stored procedure ko delete karna chahein, toh DROP PROCEDURE ka use hota hai.

•     DROP PROCEDURE GetEmployeeDetails;

Advantages of Stored Procedures:

1. Code Reusability: Ek baar likha hua procedure aap baar-baar use kar sakte hain.
2. Performance: Kyunki stored procedure precompiled hota hai, execution time better ho sakta hai.
3. Reduced Network Traffic: Agar aap complex operations ko ek procedure mein likhte hain, toh network traffic kam hota hai kyunki query ke multiple executions ki zarurat nahi hoti.
4. Better Security: Direct table access ki bajaye, aap stored procedure ko access de sakte hain, jisse security improve hoti hai.

Disadvantages of Stored Procedures:

1. Debugging and Testing: Stored procedures ko debug karna mushkil ho sakta hai.
2. Portability Issues: Stored procedures ka implementation different DBMSs mein thoda different hota hai.
3. Complexity: Agar stored procedures bahut complex ho jayein, toh unka maintenance aur understanding difficult ho sakta hai.

Conclusion:

Stored procedures SQL programming mein ek powerful tool hain jo aapko SQL operations ko efficiently aur securely manage karne mein madad karte hain. Ye performance improve karte hain, modularity provide karte hain, aur aapke code ko maintainable banate hain.