A history back to stored procedures in sql server

A complete list what we needed to create a stored procedures ?

Creating a database for test :

CREATE DATABASE StoredProcDemo
GO
USE StoredProcDemo
GO

Create Table Customer
(
CustomerID int Identity(1,1) NOT NULL,
Name Varchar(100) Not NULL,
DateOfBirth DateTime,
City Varchar(50),
State Varchar(50)
)
GO

Inserting records in tables:

INSERT INTO Customer
VALUES('Customer1','06/18/2000','Bangalore','Karnataka')

INSERT INTO Customer
VALUES('Customer2','06/10/1972','Pune','Maharastra')

INSERT INTO Customer
VALUES('Customer3','01/18/1975','Mysore','Karnataka')

INSERT INTO Customer
VALUES('Customer4','06/06/1974','Chennai','TamilNadu')

INSERT INTO Customer
VALUES('Customer5','06/18/2001','Bangalore','Karnataka')

GO

Creating a simple stored procedure:

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
Select * FROM CUSTOMER
END
----------------------------------------------

How to execute stored procedure ?

GetAllCustomers
OR
Exec GetAllCustomers
-------------------------------------

To check the content of an existing stored procedure:
sp_helptext  GetAllCustomers
--------------------------------------------------------

Stored procedure with parameters: 

CREATE PROCEDURE GetCityCustomers
@CityName varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
GO
------------------------------------

Execute/call a Stored Procedure with Parameter:

EXEC GetCityCustomers 'Bangalore'
--OR
GetCityCustomers 'Bangalore'
--OR
GetCityCustomers @CityName = 'Bangalore'
---------------------------------------------------

--Default Parameter Values:---
ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = 'BANGALORE'
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
----------------------------

Return all the customer detail:

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = NULL
AS
BEGIN
SELECT *
FROM Customer
WHERE (@CityName IS NULL OR City = @CityName)
END
----------------------------------

Store Procedure with Multiple Parameters:

Create PROCEDURE GetCustomersByCityAndState
@CityName varchar(50),
@State Varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE State = @State AND City = @CityName
END
----------------------------------

EXEC GetCustomersByCityAndState 'Bangalore','Karnataka'
--OR--
EXEC GetCustomersByCityAndState @CityName = 'Bangalore',@State ='Karnataka'
--OR--
EXEC GetCustomersByCityAndState @State ='Karnataka',@CityName = 'Bangalore'

--How to Drop an Existing Stored Procedure:---
DROP PROCEDURE GetCustomersByCityAndState
---------------------------------------------

Stored procedure with OutPut Parameters:

Create PROCEDURE GetNoOfCustomersByCity
@CityName varchar(50),
@NoofCustomers int OutPut
AS
BEGIN
SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName
END
GO
--------------------------------------------

Executing output parameter: 

Declare @cnt INT
EXEC GetNoOfCustomersByCity 'Bangalore',@cnt OUTPUT
PRINT @cnt
------------------------------

RETURN Parameter:

Create PROCEDURE GetNoOfCustomersByCityWithReturnParam
@CityName varchar(50)
AS
BEGIN
DECLARE @NoofCustomers INT

SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName

RETURN @NoofCustomers
END
GO
---------------------

Declare @cnt as int
EXEC @cnt  = GetNoOfCustomersByCityWithReturnParam 'Bangalore'
PRINT @cnt

Stored Procedure for Data Manipulation:

Create PROCEDURE AddCustomer
@Name Varchar(50),
@CityName varchar(50),
@DOB DATETIME
AS
BEGIN
INSERT INTO CUSTOMER (NAME,City,DateOfBirth)
VALUES (@Name,@CityName,@DOB)
END
GO
-------------------------------------------

Let us use the below statement to insert record in the Customer table:

EXEC AddCustomer 'Raj','Bangalore','10/23/1988'

Enjoy!