16 August 2012

Store Procedure for Beginners

Database is a important thing in the Software Development. I think, without database, we can't do the Software .So it is a Primary one. So every Program should know the Database Concept, Structures and Usages.
Store Procedure (SP):
The following Points are the Store Procedures Definition and usages:
1. Group of T-SQL statements compiled into a single execution plan.
2. Store and Retrieve information from Database.
3. T-SQL task has executed within the Application
4. Increase Performance
5. Storing a code inside in the SQL -Server
Create a Store Procedure
1. Select Query
Create Procedure SP_userlogin As Select username, password from tbl_Login
Run this Query.
2. Insert Query
Create Procedure SP_Insertuserlogin @userid varchar (20), @username varchar (50), @password varchar (50) As Insert into tbl_ Login (userid, username, password) values (@userid, @username,@ password)
3. Update Query
Create Procedure SP_Updateuserlogin @userid varchar (20), @username varchar (50), @password varchar (50) As Update tbl_Login set username=@username, password=@password where userid=@userid
4. Delete Query
Create Procedure SP_Deleteuserlogin @userid varchar (20), As Delete from tbl_Login where userid=@userid
Search the Store Procedure in the SQL SERVER
For Ex:
SP_Helptext SP_userlogin
Select the Line and Press F5. You view the Store Procedure like this
Create Procedure SP_userlogin As Select username, password from tbl_Login
If you want to Modify in your store procedure:
Alter Procedure SP_userlogin As Select distinct username, password from tbl_Login

No comments: