![]() |
What is a stored procedure? - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server) +---- Thread: What is a stored procedure? (/Thread-What-is-a-stored-procedure) Pages:
1
2
|
What is a stored procedure? - mesogloea763317 - 07-31-2023 What is a *"stored procedure"* and how do they work? What is the make-up of a stored procedure (things each *must* have to be a stored procedure)? RE: What is a stored procedure? - psychobabble681077 - 07-31-2023 Generally, a stored procedure is a "SQL Function." They have: -- a name CREATE PROCEDURE spGetPerson -- parameters CREATE PROCEDURE spGetPerson(@PersonID int) -- a body CREATE PROCEDURE spGetPerson(@PersonID int) AS SELECT FirstName, LastName .... FROM People WHERE PersonID = @PersonID This is a T-SQL focused example. Stored procedures can execute most SQL statements, return scalar and table-based values, and are considered to be more secure because they prevent SQL injection attacks. RE: What is a stored procedure? - antiliberal525153 - 07-31-2023 A stored procedure is nothing but a group of SQL statements compiled into a single execution plan. 1. Create once time and call it n number of times 2. It reduces the network traffic Example: creating a stored procedure SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetEmployee @EmployeeID int = 0 AS BEGIN SET NOCOUNT ON; SELECT FirstName, LastName, BirthDate, City, Country FROM Employees WHERE EmployeeID = @EmployeeID END GO Alter or modify a stored procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE GetEmployee @EmployeeID int = 0 AS BEGIN SET NOCOUNT ON; SELECT FirstName, LastName, BirthDate, City, Country FROM Employees WHERE EmployeeID = @EmployeeID END GO Drop or delete a stored procedure: DROP PROCEDURE GetEmployee RE: What is a stored procedure? - taft168108 - 07-31-2023 A stored procedure is a set of precompiled SQL statements that are used to perform a special task. Example: If I have an `Employee` table Employee ID Name Age Mobile --------------------------------------- 001 Sidheswar 25 9938885469 002 Pritish 32 9178542436 First I am retrieving the `Employee` table: Create Procedure Employee details As Begin Select * from Employee End To run the procedure on SQL Server: Execute Employee details --- (Employee details is a user defined name, give a name as you want) Then second, I am inserting the value into the Employee Table Create Procedure employee_insert (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int) As Begin Insert Into Employee Values (@EmployeeID, @Name, @Age, @Mobile) End To run the parametrized procedure on SQL Server: Execute employee_insert 003,’xyz’,27,1234567890 --(Parameter size must be same as declared column size) Example: `@Name Varchar(30)` In the `Employee` table the `Name` column's size must be `varchar(30)`. RE: What is a stored procedure? - dorsal290084 - 07-31-2023 A stored procedure is mainly used to perform certain tasks on a database. For example - Get database result sets from some business logic on data. - Execute multiple database operations in a single call. - Used to migrate data from one table to another table. - Can be called for other programming languages, like Java. RE: What is a stored procedure? - magicians876669 - 07-31-2023 A stored procedure is used to retrieve data, modify data, and delete data in database table. You don't need to write a whole SQL command each time you want to insert, update or delete data in an SQL database. RE: What is a stored procedure? - thwartwise651209 - 07-31-2023 A stored procedure is a named collection of SQL statements and procedural logic i.e, compiled, verified and stored in the server database. A stored procedure is typically treated like other database objects and controlled through server security mechanism. RE: What is a stored procedure? - Proparcelmentlfce - 07-31-2023 Think of a situation like this, - You have a database with data. - There are a number of different applications needed to access that central database, and in the future some new applications too. - If you are going to insert the inline database queries to access the central database, inside each application's code individually, then probably you have to duplicate the same query again and again inside different applications' code. - In that kind of a situation, you can use stored procedures (SPs). With stored procedures, you are writing number of common queries (procedures) and store them with the central database. - Now the duplication of work will never happen as before and the data access and the maintenance will be done centrally. NOTE: - In the above situation, you may wonder "Why cannot we introduce a central data access server to interact with all the applications? Yes. That will be a possible alternative. But, - The main advantage with SPs over that approach is, unlike your data-access-code with inline queries, SPs are pre-compiled statements, so they will execute faster. And communication costs (over networks) will be at a minimum. - Opposite to that, SPs will add some more load to the database server. If that would be a concern according to the situation, a centralized data access server with inline queries will be a better choice. RE: What is a stored procedure? - toughminded368 - 07-31-2023 * A stored procedure is a precompiled set of one or more SQL statements which perform some specific task. * A stored procedure should be executed stand alone using `EXEC` * A stored procedure can return multiple parameters * A stored procedure can be used to implement transact RE: What is a stored procedure? - coridon558 - 07-31-2023 In a DBMS, a stored procedure is a set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of a stored procedure can be helpful in 1. Providing a controlled access to data (end users can only enter or change data, but can't write procedures) 2. Ensuring data integrity (data would be entered in a consistent manner) and 3. Improves productivity (the statements of a stored procedure need to be written only once) |