What are stored procedures in sql server 2008? How does it work?
Query about SQL Server 2008
Stored procedures provide access control methodologies to that database. In your relational database you have several tables. The operation or functions of your choice need to be performed. Let’s take a scenario you have three tables in your database. Now you want to perform operations such as delete, update insert to any table. You need to create stored procedure of each operation.
For instance School, class, student are the table name. If you want to insert a student, you create a procedure like
Create procedure newstudent // name of procedure
@name as varchar(max), // input , you want to insert.
@classid as int,
@shoolname as varchhar(50)
as
Insert to student (name, class, school)Â values(@name,@classid,@schoolname)
Select scope_identity()
A table in a relational database is a scope and every scope is identified by its ID(identity) that is comprised by primary key.Scope_identity is used to update the inserted information to other related tables.
Likewise delete, select and update operations can also be performed. You can study it properly by going through the following link.
URL:Â http://www.sql-server-performance.com/2003/stored-procedures-basics/
Answered By
lee hung
75 points
N/A
#125781
Query about SQL Server 2008
This is a common question, so I'll try to be as precise as possible. The most used definition is: "Stored Procedure is a pre compiled a group of Transact-Sql statements, and is saved to the database under the Stored Procedures node". Once you create stored procedure you can call it from multiple places in your application.
The good sides of stored procedures are that they can provide faster code execution, reduce network traffic, and they can be executed without needing to execute any of the statements directly.
Its functionality is made available in a tightly controlled manner. This way stored procedures can provide advanced database functionality for users who wouldn't usually have access to these tasks. It basically works like this, you can create a stored procedure in the SQL Server Management Studio using the specific statement: CREATE PROCEDURE, and add the code that makes up the stored procedure. For example: "CREATE PROCEDURE MyStoredProcedure AS", SET ROW COUNT 10, SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice, FROM Products, ORDER BY Products. UnitPrice Desc.
There you have the most common stored procedure that can be used later from multiple places in your application. Hope this'll clear things a bit.
Query about SQL Server 2008
I would define Stored procedure as an already written SQL statement that is saved in the database.
If you use the same procedure multiple times it would be smart to put it into a stored procedure. Once you archive that procedure in a stored procedure, you are free to run it from the database's command environment using the appropriate exec command.
Here's an example: exec usp_displayallusers. "exec" tells SQL Server to execute the code in the stored procedure, and: usp_displayallusers" is the name of the stored procedure.
You are free to write any code you like in the store procedure, and some simple example would look like this: SELECT * FROM USERLIST. This SELECT statement will return all data in the USERLIST table, and once you save this query you can run it when and where you need it. These are the basics,
I'm sure it'll help you.
