How to create a stored procedure?

Creating a Stored Procedure:

CREATE PROCEDURE uspGetAddress // Either PROCEDURE of PROC can be used

AS

SELECT * FROM AdventureWorks.Person.Address

GO

Call in SQL:

EXEC uspGetAddress

--or just simply

uspGetAddress

Creating a Stored Procedure with parameters:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM AdventureWorks.Person.Address

WHERE City = @City

GO

Call in SQL:

EXEC uspGetAddress @City = 'New York'

• One thing to note is that you cannot use the keyword "GO" in the stored procedure.  
• Once the SQL Server compiler sees "GO" it assumes it is the end of the batch.
• Also, you can not change database context within the stored procedure such as using "USE dbName" the reason for this is because this would be a separate batch and a stored procedure is a collection of only one batch of statements.