Procedures in SQL Server

Variables:

Variables are mainly used in the scripts in SQL Server

There are two types of Variables:

  1. Global Variables.
  2. Local Variables

 Global Variables:

Global variables are predefined and read only variables. Each global variable stores system level information. Global variables are also called as System Functions.

Example:

Select @@servername -- Displays SQL Server Name
Select @@version –- Displays the SQL Server Version
Select @@rowcount –- Displays number of rows effected by last transact SQL statement.
Select @@identity –- Displays last inserted identity value in the current connection.

Local variables:

These are used to store the data temporarily. They are read and write.

Syntax:

DECLARE [@variable name] [datatype]
SET [@variable name] = [value]
PRINT ‘[message]’ + [@variable name]

Example:

DECLARE @name VARCHAR(20)
SET @name=’Hello’
PRINT @name + ‘ Gud Morning’

Type Conversion:

The process of converting one type of data into another type is called as type conversion or type casting.

Syntax:

CAST ([Expression] AS [target data type]) or
CONVERT ([target data type],[Expression],[style]) -- [style] is used for different datetime formats

Example:

DECLARE @i INT
SET @i = 400
PRINT ‘The value of i is:’+ CAST(@i as varchar)

Conditional Statement (if….else):

Syntax:

IF (condition)
BEGIN
  …………
END
ELSE
BEGIN
  ……
END

Example:

DECLARE @i INT
SET @i=10
IF (@i<20)
BEGIN
  PRINT CAST (@i AS INT) + ‘ is less than 20’
END
ELSE
BEGIN
  PRINT CAST (@i AS INT) + ‘ is greater than 20’
END

BEGIN and END are optional for single statement in the if Block, but it is mandatory in if block if there are multiple statements. It is recommended to write begin and end in the if block.

Looping (While):

There is only one looping in SQL Server i.e. While loop.

Syntax:

WHILE (condition)
BEGIN
  ……….
END

Example:

DECLARE @i INT
SET @i=1
WHILE (@i<=10)
BEGIN
  PRINT ‘Value of I is:’ + CAST(@i AS VARCHAR)
SET @i=@i+1
END

CASE:

Case is used as switch loop. It evaluates a list of conditions and returns one of multiple possible result expressions.

Syntax:

SELECT 
  CASE [expression]
    WHEN [value] THEN [statements]
    WHEN [value] THEN [statements]
    ………………………..
   ELSE [statements]
  END

Example:

DECLARE @i INT
SET @i=10
SELECT 
  CASE @i
    WHEN 10 THEN ‘Ten’
    WHEN 20 THEN ‘Twenty’
    WHEN 30 THEN ‘Thirty’
    WHEN 40 THEN ‘Forty’
    ELSE ‘Invalid’
  END

Else is optional, it is used when the value given in case is not found.

Stored Procedure:

Stored Procedure is a set of Pre Compiled Transact SQL Statement stored only database server. A stored procedure takes input and output parameters. There are two types of stored procedures:

System Stored Procedures (starts with sp_).
User Defined stored Procedures (starts with usp_).

They can accept input parameters and return multiple values in the form of output parameters to the calling procedure. The benefits of Stored Procedures in SQL Server rather than T-SQL :

    1. They are registered at the server.
  • They can reduce network traffic.

 

Syntax:

CREATE PROCEDURE [proc-name]
AS
BEGIN
  SET NOCOUNT ON
  SET QUOTED_IDENTIFIER ON
  SET ANSI_NULLS ON

  /*
  Created By:
  Procedure Name:
  Created On:
  Parameters:
  Purpose:
  Execution:
  Output:
  Modified By:
  Modified History:
  */

  SET ANSI_NULLS OFF
  SET QUOTED_IDENTIFIER OFF

  SET NOCOUNT OFF
END

User Stored Procedures:

We can create user defined stored procedures.

Syntax:

CREATE PROCEDURE [proc name]
AS
BEGIN
  …………….
END

Example:

CREATE PROCEDURE usp_add(@a INT, @b INT)
AS
BEGIN
  DECLARE @c INT
  SET @c=@a+@b
  PRINT ‘Addition of ‘+CAST(@a AS VARCHAR)+’ and ‘+CAST(@b AS VARCHAR)+’ is:’+CAST(@c AS VARCHAR)
END

EXEC usp_add 1,2

<strong>Output: </strong>
Addition of 1 and 2 is:3

Views and stored procedures can be viewed by using the table syscomments. The content of a procedure can be viewed by using sp_helptext.

Output Parameters:

We can also provide output parameters to the stored procedures.

Example:

CREATE PROCEDURE usp_add_num(@a INT, @b INT, @c INT OUT)
AS
BEGIN
  SET @c=@a+@b
END

DECLARE @x INT
EXEC usp_add_num 2,3,@x OUT
PRINT @x

<strong>Output :</strong> 5
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: