SQL Server's Categorization of Stored Procedures based on Input and Output Parameters

Introduction

Today, we are going to learn about another topic in SQL (Structured Query Language) Server or also popularly known as Microsoft SQL Server. The topic is about Stored Procedures in SQL Server. This Topic vividly and deeply explains about the input and output parameters which are used in SQL server. Without these parameters the Stored Functions or also known as Stored Procedures concept is not going to run or used in SQL Server.

Stored Procedure

The Stored Procedures are used to reduce the concept of redundancy in SQL Server. There come some cases where same code is written more than once for the result of output. So, in this situation we used Stored Procedures. This Stored Procedure is called as Functions in Oracle SQL Server. This gives a lot of relaxation to the SQL Developer.

This Stored Procedure concept is similar to the functions or method used in other Programming Languages. There you are going to create them in the Program or any other Program. Then, when required we are going to call them and then use their resources. We can call them infinite times.

Procedure Syntax in SQL Server

CREATE PROCEDURE Procedure Name (Parameters if available)
AS
BEGIN
SQL Query based on our Requirement
GO

Example 1 (Without Parameters)

1> CREATE PROCEDURE PLAYERDEETS
2> AS
3> BEGIN
4> select * from ipla where sid > 2
5> END
6> GO
Executable Statement
1> EXEC PLAYERDEETS
2> GO

Example 2 (With Parameters)

1> CREATE PROCEDURE PLAYERDEETS
2> (@ID int, @NAME varchar(10), @AGE int, @SAL float)
3> AS
4> BEGIN
5> SELECT @ID, @NAME, @AGE, @SAL AS Result
6> END
7> GO
Executable Statement
1> EXEC PLAYERDEETS 1, 'miller', 33, 8.6
2> GO

Types of SQL Server Stored Procedure Parameters

There are two types of parameters in Stored Procedure Parameters Concept. They are:

With Parameters
Without Parameters

The With Parameters concept is also divided into two types. They are:

Dynamic Parameters or Input Parameters
Static Parameters or Output Parameters

First, let us learn about SQL Server Stored Procedure which is executed without Parameters.

SQL Server Stored Procedure which is executed without Parameters

The Stored Procedure created in SQL Server without any parameters is known as SQL Server Stored Procedure without any parameters.

Syntax

CREATE PROCEDURE Procedure name 
AS
Radom SQL Query 
GO


EXEC Procedure name;
GO

Example 1

1> CREATE PROCEDURE PLAYERDEETS
2> AS
3> BEGIN
4> select * from ipla 
5> END
6> GO
Executable Statement
1> EXEC PLAYERDEETS
2> GO

Output

ID   NAME                       SAL       COMMISION_PERCENTAGE   	MONTHLY_SALARY
_ _   _ _ _ _ _ _ _ _        _ _ _    _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _  _ _ _ _ _ _ _ _ _ _ _ _
 1    Joe Root               75000          	0.15                    		86250
 2    Ros Taylor          	90000           	0.1                     		99000
 3    Paul Adams       	    50000         		0.01                    		50500
 4    Victor Lee          	43000          		0                       		43000
 5    Matt Potts        	20000          		0                       		20000
 6    James Anderson        200000        		0.33                   		    266000
 7    Craig Overton         11000          		0                       		11000
 8    Rory Burns             9000               0                      			 9000
(8 rows affected)

Example 2

1> CREATE PROCEDURE PLAYERDEETS1
2> AS
3> BEGIN
4> select * from iplb
5> END
6> GO
Executable Statement
1> EXEC PLAYERDEETS1
2> GO

Output

SID	 SNAME             SAL        AGE
_ _ _	 _ _ _ _ _       _ _ _ _    _ _ _ _ 
 1	 mahi               12         	 40
 2 	kohli               14           33
 3	 DK                 6.25         33
 4 	warner              6.75         33
 5 	rahul               16           29
 6 	pandya              14           27
 (6 rows affected)

SQL Server Stored Procedure which is executed with Parameters

The Stored Procedure created in SQL Server with any parameters is known as SQL Server Stored Procedure with any parameters. The number of parameters used is dependent on our interest.

Syntax

CREATE PROCEDURE Procedure name (@Parameter name Data type (Size if required)
AS
Radom SQL Query 
GO


EXEC Procedure name If any input required;
GO

Example

1> CREATE PROCEDURE PLAYERDEETS
2> (@ID int, @NAME varchar(10), @AGE int, @SAL float)
3> AS
4> BEGIN
5> SELECT @ID, @NAME, @AGE, @SAL AS Result
6> END
7> GO
Executable Statement
1> EXEC PLAYERDEETS 1, 'miller', 33, 8.6
2> GO

Output

                                   				Result
_ _ _ _ _       _ _ _ _ _ _ _ _      _ _ _ _     _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _  
          	1 	       miller                 33                  	    8.5999999999999996

We all know this SQL Server Stored Procedure which is executed with Parameters type is divided in to two types. They are:

Dynamic Parameters or Input Parameters
Static Parameters or Output Parameters

SQL Server Stored Procedure which is executed with Input Parameters

The Stored Procedure created in SQL Server with any parameters is known as SQL Server Stored Procedure with any parameters. The number of parameters used is dependent on our interest.

If the Parameters are given in the Given EXEC Statement, then the parameters passed are known as SQL Server Stored Procedure Input Parameters.

These conditions make the above SQL Server Stored Procedure with parameters into SQL Server Stored Procedure which is executed with Input Parameters

Input Parameters Syntax

CREATE PROCEDURE Procedure name (@Parameter name Data type (Size if required)
AS
Radom SQL Query 
GO


EXEC Procedure name If any input required;
GO

Examples

1> CREATE PROCEDURE EMPRole (@Role varchar(30))
2> AS
3> SELECT *  FROM empdeets WHERE @Role =Role
4> GO
Executable Statement
1> EXEC PLAYERDEETS "TECHIE"
2> GO

Empdeets table

ID   NAME                       SAL      TOTAL_SALARY                          		 ROLE
_ _   _ _ _ _ _ _ _ _        _ _ _    _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _  _ _ _ _ _ _ _ _ _ _ _ _
  Joe Root              	75000          		75000                    		TECHIE
  Ros Taylor          	    90000           	90000		                     TECHIE
  Paul Adams       	        50000         		50000         		             Manager
  Victor Lee          	    43000          		43000                             TECHIE
  Matt Potts        	   20000          		20000                       	  TECHIE
  James Anderson            200000              200000        		             TECHIE
  Craig Overton            11000                11000                       	  MAINTAIN
  Rory Burns                9000           	    9000                      		SUPPLY

Output

ID   NAME                       SAL      TOTAL_SALARY                          		 ROLE
_ _   _ _ _ _ _ _ _ _        _ _ _    _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _  _ _ _ _ _ _ _ _ _ _ _ _
 1    Joe Root              75000          		75000                    		TECHIE
 2    Ros Taylor          	90000           	90000		                     TECHIE
 4    Victor Lee          	43000          	    43000                            TECHIE
 5    Matt Potts        	20000          		20000                            TECHIE
 6    James Anderson        200000              200000        		             TECHIE

SQL Server Stored Procedure which is executed with Output Parameters

The Stored Procedure created in SQL Server with any parameters is known as SQL Server Stored Procedure with any parameters. The number of parameters used is dependent on our interest.

Here the parameters mentioned in the Procedure is denoted with out statement, then the parameter turns into SQL Server Stored Procedure with Output Parameters.

If the Parameters are given in the Given EXEC Statement, then the parameters passed are known as SQL Server Stored Procedure Output Parameters.

These conditions make the above SQL Server Stored Procedure with parameters into SQL Server Stored Procedure which is executed with Output Parameters

Output Parameters Syntax

CREATE PROCEDURE Procedure name (@Parameter name Data type (Size if required) out)
AS
Radom SQL Query 
GO


EXEC Procedure name If any input required;
GO

Examples

Empdeets table

ID   NAME                       SAL      TOTAL_SALARY                           BIRTH_YEAR
_ _   _ _ _ _ _ _ _ _        _ _ _    _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _  _ _ _ _ _ _ _ _ _ _ _ _
  Joe Root              75000          		75000                    		1990
  Ros Taylor          	 90000           	90000		                     1990
  Paul Adams       	    50000         		50000         		             1990
  Victor Lee          	43000          		 430000                       	 1990
  Matt Potts        	20000          		20000                       	 1990
  James Anderson        200000              200000        		             1990
  Craig Overton         11000          	    11000                            1999
  Rory Burns             9000               9000                      		1997

1> DECLARE @countNum INT;
2>
3> EXEC uspFindCount
4>     @birth_year = 1990,
5>     @head_count = @count OUTPUT;

Executable Statement
1> SELECT @count AS 'Employees born on ';
2> END
3> GO

Output

Employees born on
_ _ _ _ _ _ _ _ _ _ _
		6

SQL Server Stored Procedure which is executed with both Input and Output Parameters

The Stored Procedure created in SQL Server with any parameters is known as SQL Server Stored Procedure with any parameters. The number of parameters used is dependent on our interest.

Here the parameters mentioned in the Procedure is denoted with out statement, then the parameter turns into SQL Server Stored Procedure with Output Parameters.

If the Parameters are given in the Given EXEC Statement, then the parameters passed are known as SQL Server Stored Procedure Output Parameters

These any of the conditions make the above SQL Server Stored Procedure with parameters into SQL Server Stored Procedure which is executed with Output or Input Parameters.

If the both Input and Output type of parameters mentioned makes the SQL Server Stored Procedure into SQL Server Stored Procedure which is executed with both Input and Output Parameters.

Output Parameters Syntax

CREATE PROCEDURE Procedure name 
(@Parameter1 name Data type (Size if required) out  
   @Parameter2 name Data type (Size if Required))
AS
Radom SQL Query 
GO


EXEC Procedure name If any input required;
GO

This is all about SQL Server's categorization of stored procedures based on input and output parameters

Next TopicUse of Single Quotes for Stored Procedure Parameters in SQL Server

← prev next →