Stored​ ​Procedures In SQL Server | What is Stored Procedures In SQL Server?

Working​ ​with​ ​Stored​ ​Procedures

We have been using and executing mostly single statements. However, in lots of cases you could need to execute multiple operations. Here is where STORED PROCEDURES can be used. Stored Procedures can be very complicated to write.

STORED PROCEDURES are collections of one or more SQL statements saved for future use. They are like batch files but much more. Using STORED PROCEDURES requires first knowing how to execute them.

Let’s take a look at a simple STORED Procedure.

Example:

The following SQL code shows how to execute a stored procedure and any parameters that need to be passed.

Note: This actually will not​ ​run​ because we first have to create the stored procedure. This is just showing you how to write code to execute.

Basic Syntax Sample

--This code executes a stored procedure and any parameters 
Execute productpricing              @cheap OUTPUT, 
                                    @expensive OUTPUT, 
                                    @average OUTPUT

Creating​ ​Stored​ ​Procedures

Lets first look at a simple stored procedure that returns the average product price.

Example:

The following example creates a stored procedure without parameters. We use the CREATE statement to create stored procedures and the BEGIN and END statements to enclose the code to run. This stored procedure returns the average product price.

It is always a good idea to add a prefix to stored procedures you create. This distinguishes your user stored procedures from the system stored procedures.

Write​ ​Exercise​ ​Code:

--Create sp to return average product price 
CREATE PROCEDURE usp_productpricing AS 
BEGIN 
	SELECT Avg(prod_price) AS priceaverage 
	FROM products 
END; 

Example:

Now let’s execute the new stored procedure we just created.

The following code runs the stored procedure called usp_productpricing

Write​ ​Exercise​ ​Code:

--This code executes the stored procedure 
Exec usp_productpricing 

Result

priceaverage

6.823333

Example:

Now let’s DROP or delete the new stored procedure we just created.

The following code DROPS the stored procedure called usp_productpricing

Write​ ​Exercise​ ​Code

--This code DROPS the named stored procedure 
DROP PROCEDURE usp_productpricing 

Working​ ​with​ ​Parameters

The previous example was a simple stored procedure that displayed the results set of a select statement. Stored procedures can also be used to return results to variables that you specify.

Example:

The following example alters a stored procedure to add three parameters and specify the datatype as money. We also add three separate select statements to populate each of the parameters with a value when the stored procedure is executed.

Because we want to display the contents we use OUTPUT after each parameter. The OUTPUT is used to specify that the parameters are used to send data out. Otherwise the code would just populate the parameters and not send out the content to the stored procedure (user who executed).

Write​ ​Exercise​ ​Code:

Create PROCEDURE [dbo].[usp_productpricing] 
@price_min Money OUTPUT, 
@price_max Money OUTPUT, 
@price_avg Money OUTPUT 

AS 
BEGIN 
	SELECT @price_min= Min(prod_price) 
	FROM products; 

	SELECT @price_max= Max(prod_price) 
	FROM products; 

	SELECT @price_avg= Avg(prod_price) 
	FROM products; 
END; 

Now we need to call the altered stored procedure. Because we want to display the content of the parameters, the first thing we have to do is specify (declare)​ ​three variables​ ​to store the values from the parameters.

Next, we need to execute stored procedure to populate our new variables with parameter values that can be used.

Finally, we run a Select statement like the one below to display the contents of the variables. All code must be executed all at the same time

Write​ ​Exercise​ ​Code

--This declares variables to store values from parameters 
DECLARE @cheap Money 
DECLARE @expensive Money 
DECLARE @average Money 
--This code executes stored procedure and populates variables 
exec usp_productpricing @cheap OUTPUT, 
    @expensive OUTPUT, 
    @average OUTPUT 
    
--This code displays variable vaules that are passed from the parameters. SELECT 
    @cheap AS Cheap, 
        @expensive AS Expensive, 
        @average AS Average

Result

Cheap Expensive Average

3.4900 11.9900 6.8233

Example

Here is another example of a stored procedure. In this stored procedure we actually pass in a parameter to the stored procedure as well as return an OUTPUT parameter.

This new stored procedure called usp_ordertotal accepts an order number and then returns a result set for that order.

Write​ ​Exercise​ ​Code:

CREATE PROCEDURE usp_ordertotal 
	@order_num INT, 
	@order_total Money OUTPUT 
AS 
BEGIN 
	SELECT 
		@order_total = SUM(item_price*quantity) 
	FROM 
		orderitems 
	WHERE order_num = @order_num; 
END; 

Result

To execute this stored procedure we use the following code. Here two parameters are passed to the stored procedure. order_num is passed to use in the WHERE clause to return the correct order number. The order_total is passed which contains the calculated value. All code must be executed at the same time.

Write Exercise Code:

DECLARE @order_total Money 
EXECUTE usp_ordertotal 20009, @order_total OUTPUT 
SELECT @order_total AS OrderTotal

Result

OrderTotal

1867.5000

Intelligent​ ​Stored​ ​Procedures

So far we have been working with simple stored procedures. The real power of stored procedures come in when you implement business rules and intelligent processing.

The following example is more complex. It not only obtains the order total it also determines if a tax is applicable based on a condition.

Example:

The following example is more complex. It not only obtains the order total it also determines if a tax is applicable based on a condition. We add three new variables called @taxable, @taxrate and @total. We also add an IF statement to determine when the tax is added or not added. We use the ALTER statement to modify existing stored procedure.

Write​ ​Exercise​ ​Code:

-- Name: ordertotal 
-- Parameters: @order_num = order number -- @taxable = 0 if not taxable, 1 if taxable -- @order_total = order total variable 

ALTER PROCEDURE usp_ordertotal 
	@order_num INT, 
	@taxable BIT, 
	@order_total Money OUTPUT 
AS 
BEGIN 
	--Declare variable for total 
	DECLARE @total Money; 
	--Declare tax percentage 
	DECLARE @taxrate INT; 
	--Set tax rate 
	SET @taxrate = 6; 
	--Get the order total 
	SELECT 
		@total = SUM(item_price*quantity) 
	FROM 
		orderitems 
	WHERE 
		order_num = @order_num 
	--Is this taxable? 
	If @taxable = 1 
		--Yes, so add taxrate to the total 
		SET @total=@total+(@total/100*@taxrate); 
	-- And finally, save to OUTPUT variable 
	SELECT @order_total= @total; 
END;

Example:

To execute this stored procedure we use the following code. Here we pass in the order number, where it is taxable or not and the ordertotal as OUTPUT. All code must be executed at the same time.

Write​ ​Exercise​ ​Code:

DECLARE @order_total Money 
EXECUTE usp_ordertotal 20005, 0, @order_total OUTPUT 
SELECT @order_total AS OrderTotal

Result

OrderTotal

1648.0000

Send your request at realcode4you@gmail.com and get instant help with an affordable price.

We are always focus to delivered unique or without plagiarism code which is written by our highly educated professional which provide well structured code within your given time frame.

If you are looking other programming language help like C, C++, Java, Python, PHP, Asp.Net, NodeJs, ReactJs, etc. with the different types of databases like MySQL, MongoDB, Oracle, etc. then also contact us.