Total Page Preview: 000000001508
What is SQL Stored Procedures for SQL Server in English and Hindi
SQL store procedure in English
SQL Server Stored Procedures are a group of SQL statements that are saved in the server's database and can be executed on demand. Stored Procedures provide a lot of advantages over the direct SQL statements execution, like security, efficiency, and maintainability. They are useful when you need to perform a series of SQL operations on a regular basis, or when you need to encapsulate complex SQL logic that can be reused.
Here is a detailed example of how to create and use a Stored Procedure in SQL Server:
Let's assume we have a database called "Sales" with a table named "Orders". This table has columns such as OrderID, CustomerName, OrderDate, OrderAmount, etc. We want to create a stored procedure that will retrieve all orders for a specific customer name.
1-First, we need to create a stored procedure. Here's the SQL code to create a Stored Procedure:
In this example, we have created a stored procedure named "GetOrdersByCustomerName". This stored procedure accepts a single parameter called "@CustomerName" of type VARCHAR(50). The SQL code inside the stored procedure selects all the rows from the Orders table where the CustomerName is equal to the @CustomerName parameter.
2-Once the Stored Procedure is created, we can execute it by calling it with the parameter value. Here's the SQL code to execute the stored procedure:
In this example, we are executing the "GetOrdersByCustomerName" stored procedure with the parameter value of "John Doe". This will retrieve all the orders from the Orders table where the CustomerName is equal to "John Doe".
Stored Procedures can also return values. For example, we can modify our previous Stored Procedure to return the number of orders for the specified customer name:
In this example, we have modified the Stored Procedure to accept two parameters. The first parameter is the @CustomerName parameter, and the second parameter is an OUTPUT parameter called @OrderCount, which will be used to return the number of orders.
To execute this Stored Procedure and retrieve the @OrderCount value, we need to use the following SQL code:
In this example, we are declaring a variable called @OrderCount of type INT, and then executing the Stored Procedure with the parameter value of "John Doe" and the @OrderCount variable as the OUTPUT parameter. Finally, we are selecting the @OrderCount variable to retrieve the number of orders.
Stored Procedures are a powerful feature of SQL Server that can help you encapsulate complex SQL logic, improve security, and increase efficiency.
SQL store procedure in Hindi
SQL stored procedure ek reusable database program hota hai jo SQL statements ki set ko ek single logical unit mein combine karta hai. Stored procedures database server par store kiye jaate hai aur unhe client applications se call kiya ja sakta hai.
Stored procedures ke kai benefits hai, jaise ki:
-
Performance improvement: Stored procedures ki execution database server par hoti hai, jisse network traffic kam ho jaata hai aur query execution time bhi reduce ho jaati hai.
-
Code reusability: Stored procedures ko ek baar likhne ke baad baar baar use kiya ja sakta hai.
-
Security: Stored procedures ke through database access ko control kiya ja sakta hai, jisse unauthorized access aur SQL injection jaise attacks se bacha ja sakta hai.
Yahan ek example hai SQL stored procedure ka:
Iss stored procedure mein get_customer_details naam ka ek stored procedure banaya gaya hai jiske liye @customer_id parameter ka use kiya jaata hai. Iss stored procedure mein customers table se customer ki details select ki jaati hai jiska customer_id parameter ke through value pass kiya jaata hai.
Iss stored procedure ko call karne ke liye, neeche diya gaya code ka use kiya ja sakta hai:
Iss code se get_customer_details stored procedure ko customer_id ke value ke saath call kiya ja sakta hai.
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com