It is a precompiled set of sql statements. It is normally used where you have to work of big or complex queries or have some checks before executing any queries. Stored Procedures are not case sensitive.
Create a stored procedure
CREATE PROCEDURE spGETDateASSELECT GETDATE() AS TodaysDate
Alter a stored procedure
It is used when you have to make some changes to the existing procedure.
ALTER PROCEDURE spGETDateASSELECT GETDATE() + 1 AS NextDate
Passing variables to a stored procedure
You can pass any number of variables,but the variables should be defined with the data types and the variable name should be preceded with an @ symbol.
CREATE PROCEDURE spGETDate@daystoaddorsubtract INTASSELECT GETDATE() AS TodaysDate,GETDATE() + @daystoaddorsubtract AS 'ModifiedDate'
Calling the stored procedure
Procedure name and the values passed as parameters
Stored Procedure with many checks and computation.
I am providing a basic example. many complex procedures can be written as per the requirement.
This procedure checks whether the passed data exist. If exist then it returns a -1 . If the data doesnt exist it inserts the data and returns the last inserted identity value. If the insert statement fails then it will return a 0 to indicate an error.
Thanks to Roopesh Thakkar for giving a hint about this interesting stored procedure.
Note: To run this procedure you should have a table named mytable with columns id,Vname,VAddress. id is a primary key with identity 1 and Vname and VAddress are varchar with size 1000
CREATE PROCEDURE spInsertMYTABLE@Name VARCHAR(1000),@Address VARCHAR(1000)ASIF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)BEGINDECLARE @error INTINSERT INTO mytable(VName,VAddress)VALUES(@Name,@Address)SET @error = @@errorIF @error=0SELECT scope_identity() AS 'ReturnValue'ELSESELECT 0 AS 'ReturnValue'ENDELSESELECT -1 AS 'ReturnValue'
I really liked the concept of stored procedures. It will make your life more simpler when you deal with complex and big queries.