SQL stored procedures in Windows Azure SQL database

Using stored procedures in Windows Azure SQL database is a conventional wisdom. Store procedures typically run faster than Adhoc SQL statements. The reason for this is that they are pre-compiled and SQL Server has generally a good idea about the execution plan it needs to take. Another advantage is that with stored procedures, you can use input and output parameters. You can call one store procedure from within another procedure. One of biggest benefit of using stored procs is code reuse, the concept you build it once and use it over and over again maybe from different application or interfaces. The Transact or TSQL code within the store procedure will run together as a unit and is typically used to do certain tasks like outputting a certain report, returning some sort of value back to the client application or at the very least execute a collection of SQL statements together as a batch. We are going to show you a simple example of using stored procedure utilizing Windows Azure SQL database.

/*————————————————————————————–

We create a simple stored procedure here:

-passing two parameters, city and country

-run a select statement on customers table

————————————————————————————–*/

 –SIMPLE PROCEDURE TO PULL CUSTOMER DATA BY CITY AND COUNTRY

 

Create PROCEDURE [dbo].[PROC_CUSTOMER]

–PASSING IN TWO PARAMETERS

@City VARCHAR(50),

@COUNTRY VARCHAR(50)

AS

–THIS SUPPRESSES MESSAGES ABOUT NUMBER OR ROWS RETURNED

SET NOCOUNT ON

SELECT

CustomerID

,CompanyName

,ContactName

,ContactTitle

,Address

,City

,Region

,PostalCode

,Country

FROM CUSTOMERS

WHERE

CITY=@City

AND

COUNTRY=@COUNTRY

/*

DECLARE

@CITY VARCHAR(50),

@COUNTRY VARCHAR(50)

SET @CITY=’LONDON’

SET @COUNTRY=’UK’

EXEC PROC_CUSTOMER @CITY, @COUNTRY

–OR

EXEC PROC_CUSTOMER ‘LONDON’, ‘UK’

*/

GO

 

This entry was posted in Development, SQL Programming, TSQL and tagged , . Bookmark the permalink.

Comments are closed.