Stored Procedures

A Stored Procedure is a precompiled collection of SQL statements. In a stored procedure you can use if sentence, declare variables, etc.

Syntax for creating a Stored Procedure:

CREATE PROCEDURE <ProcedureName> @<Parameter1> <datatype>
…
declare
@myVariable <datatype>
… Create your Code here

Note! You need to use the symbol “@” before variable names.

Syntax for using a Stored Procedure:

EXECUTE <ProcedureName(…)>

Example:

We use the SCHOOL and CLASS tables as an example for our Stored Procedure. We want to create a Stored Procedure that lists all the existing schools and the belonging classes.

We create the Stored Procedure as follows:

CREATE PROCEDURE GetAllSchoolClasses 
AS

select 
SCHOOL.SchoolName, 
CLASS.ClassName 
from
SCHOOL
inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId 
order by SchoolName, ClassName

When we have created the Stored Procedure we can run (or execute) the Stored procedure using the execute command like this:

execute GetAllSchoolClasses

We can also create a Store Procedure with input parameters.

Example:

We use the same tables in this example (SCHOOL and CLASS) but now we want to list all classes for a specific school.

The Stored Procedure becomes:

CREATE PROCEDURE GetSpecificSchoolClasses 
@SchoolName varchar(50)
AS

select 
SCHOOL.SchoolName, 
CLASS.ClassName 
from
SCHOOL
inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId 
where SchoolName=@SchoolName
order by ClassName

We run (or execute) the Stored Procedure:

execute GetSpecificSchoolClasses 'TUC'

or:

execute GetSpecificSchoolClasses 'NTNU'

When we try to create a Stored Procedure that already exists we get the following error message:

There is already an object named ‘GetSpecificSchoolClasses’ in the database.

Then we first need to delete (or DROP) the old Stored Procedure before we can recreate it again.

We can do this manually in the Management Studio in SQL like this:

A better solution is to add code for this in our script, like this:

IF EXISTS (SELECT name
       FROM	sysobjects
       WHERE name = GetSpecificSchoolClasses ' 
       AND	type = 'P')
   DROP PROCEDURE GetSpecificSchoolClasses
GO

CREATE PROCEDURE GetSpecificSchoolClasses 
@SchoolName varchar(50)
AS

select 
SCHOOL.SchoolName, 
CLASS.ClassName 
from
SCHOOL
inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId 
where SchoolName=@SchoolName
order by ClassName

So we use CREATE PROCEDURE to create a Stored Procedure and we use DROP PROCEDURE to delete a Stored Procedure.

NOCOUNT ON/NOCOUNT OFF

In advanced Stored Procedures and Script, performance is very important. Using SET NOCOUNT ON and SET NOCOUNT OFF makes the Stored Procedure run faster.

SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Example:

IF EXISTS (SELECT name
           FROM	sysobjects
           WHERE name = 'sp_LIMS_IMPORT_REAGENT'
           AND	type = 'P')
        DROP PROCEDURE sp_LIMS_IMPORT_REAGENT
GO

CREATE PROCEDURE sp_LIMS_IMPORT_REAGENT
@Name varchar(100), 
@LotNumber varchar(100), 
@ProductNumber varchar(100), 
@Manufacturer varchar(100)

AS
SET NOCOUNT ON

if not exists (SELECT ReagentId FROM LIMS_REAGENTS WHERE [Name]=@Name)
       INSERT INTO LIMS_REAGENTS ([Name], ProductNumber, Manufacturer) 
       VALUES (@Name, @ProductNumber, @Manufacturer)
else
UPDATE LIMS_REAGENTS SET
       [Name] = @Name,
       ProductNumber = @ProductNumber, 
       Manufacturer = @Manufacturer, 
       WHERE [Name] = @Name

SET NOCOUNT OFF
GO

This Stored Procedure updates a table in the database and in this case you don’t normally need feedback, sp setting SET NOCOUNT ON at the top in the stored procedure is a good idea. it is also good practice to SET NOCOUNT OFF at the bottom of the stored procedure.