Stored Procedure

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 spGETDate

AS

SELECT GETDATE() AS TodaysDate

Alter a stored procedure

It is used when you have to make some changes to the existing procedure.

ALTER PROCEDURE spGETDate

AS

SELECT 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 INT

AS

SELECT GETDATE() AS TodaysDate,GETDATE() + @daystoaddorsubtract AS 'ModifiedDate'

Calling the stored procedure

Procedure name and the values passed as parameters

spGETDATE 29

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)

AS

IF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)

BEGIN

DECLARE @error INT

INSERT INTO mytable(VName,VAddress)

VALUES(@Name,@Address)

SET @error = @@error

IF @error=0

SELECT scope_identity() AS 'ReturnValue'

ELSE

SELECT 0 AS 'ReturnValue'

END

ELSE

SELECT -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.