Store proc

Overview
A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

CREATE PROCEDURE sp_name
AS
SELECT * FROM AdventureWorks.Person.Address
GO

EXEC sp_name

input parameter@@@

CREATE PROCEDURE sp_name @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

EXEC sp_name @City = 'Delhi'

default parameter:---

CREATE PROCEDURE Sp_name @City nvarchar(30) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
GO

MUltiple para:::

CREATE PROCEDURE sp_name @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO

excute:-
EXEC Sp_name @City = 'Calgary'
--or
EXEC sp_name @City = 'Calgary', @AddressLine1 = 'A'
--or
EXEC sp_name @AddressLine1 = 'Acardia'

out para@@@@@

CREATE PROCEDURE Sp_name @City nvarchar(30), @AddressCount int OUT
AS
SELECT @AddressCount = count(*) 
FROM AdventureWorks.Person.Address 
WHERE City = @City

2nd step:---

DECLARE @AddressCount int
EXEC Sp_name @City = 'Calgary', @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount


@@@ error handling....

CREATE PROCEDURE uspTryCatchTest
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

------------------