Optional parameters in sql server stored procedures

Parameters of a sql server stored procedure can be made optional by specifying default values.

We wil be using table tblEmployee for this Demo.
CREATE TABLE tblEmployee
(
Id int IDENTITY PRIMARY KEY,
Name nvarchar(50),
Email nvarchar(50),
Age int,
Gender nvarchar(50),
HireDate date,
) 

Insert into tblEmployee values
('Sara Nan','Sara.Nan@test.com',35,'Female','1999-04-04')
Insert into tblEmployee values
('James Histo','James.Histo@test.com',33,'Male','2008-07-13')
Insert into tblEmployee values
('Mary Jane','Mary.Jane@test.com',28,'Female','2005-11-11')
Insert into tblEmployee values
('Paul Sensit','Paul.Sensit@test.com',29,'Male','2007-10-23')

Name, Email, Age and Gender parameters of spSearchEmployees stored procedure are optional. Notice that, we have set defaults for all the parameters, and in the "WHERE" clause we are checking if the respective parameter IS NULL.
Create Proc spSearchEmployees
@Name nvarchar(50) = NULL,
@Email nvarchar(50) = NULL,
@Age int = NULL,
@Gender nvarchar(50) = NULL
as
Begin
Select * from tblEmployee where
(Name = @Name OR @Name IS NULL) AND
(Email = @Email OR @Email IS NULL) AND
(Age = @Age OR @Age IS NULL) AND
(Gender = @Gender OR @Gender IS NULL) 
End

Testing the stored procedure
1. Execute spSearchEmployees -- This command will return all the rows
2. Execute spSearchEmployees @Gender = 'Male' -- Retruns only Male employees
3. Execute spSearchEmployees @Gender = 'Male', @Age = 29 -- Retruns Male employees whose age is 29

This stored procedure can be used by a search page that looks as shown below. 
 

Logon triggers in sql server

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.  

Logon triggers can be used for
1. Tracking login activity
2. Restricting logins to SQL Server
3. Limiting the number of sessions for a specific login 

Logon trigger example : The following trigger limits the maximum number of open connections for a user to 3. 

CREATE TRIGGER tr_LogonAuditTriggers

ON ALL SERVER

FOR LOGON

AS

BEGIN

    DECLARE @LoginName NVARCHAR(100)

    Set @LoginName = ORIGINAL_LOGIN()

    IF (SELECT COUNT(*) FROM sys.dm_exec_sessions

         WHERE is_user_process = 1

         AND original_login_name = @LoginName) > 3

    BEGIN

         Print 'Fourth connection of ' + @LoginName + ' blocked'

         ROLLBACK

    END

END

An attempt to make a fourth connection, will be blocked. 
 

The trigger error message will be written to the error log. Execute the following command to read the error log.
Execute sp_readerrorlog 

How to find dependencies of object

There are several ways to find object dependencies in SQL Server

1. View Dependencies feature in SQL Server Management Studio 
2.  sp_depends system stored procedure - 

3. SQL Server dynamic management functions 

     sys.dm_sql_referencing_entities
     sys.dm_sql_referenced_entities

How to find dependencies using SQL Server Management Studio
Use View Dependencies option in SQL Server Management studio to find the object dependencies 

For example : To find the dependencies on the Employees table, right click on it and select View Dependencies from the context menu 

 

In the Object Dependencies window, depending on the radio button you select, you can find the objects that depend on Employees table and the objects on which Employeestable depends on. 

 

Identifying object dependencies is important especially when you intend to modify or delete an object upon which other objects depend. Otherwise you may risk breaking the functionality. 

For example, there are 2 stored procedures (sp_GetEmployees and sp_GetEmployeesandDepartments) that depend on the Employees table. If we are not aware of these dependencies and if we delete the Employees table, both stored procedures will fail with the following error.

Msg 208, Level 16, State 1, Procedure sp_GetEmployees, Line 4
Invalid object name 'Employees'.

There are other ways for finding object dependencies in SQL Server which we will discuss in our upcoming videos. 



sp_depends
A system stored procedure that returns object dependencies
For example,

• If you specify a table name as the argument, then the views and procedures that depend on the specified table are displayed
• If you specify a view or a procedure name as the argument, then the tables and views on which the specified view or procedure depends are displayed.

Syntax :Execute sp_depends 'ObjectName'

The following SQL Script creates a table and a stored procedure

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Create procedure sp_GetEmployees

as

Begin

    Select * from Employees

End

Go

Returns the stored procedure that depends on table Employees

sp_depends 'Employees'

Ouptut :


Returns the name of the table and the respective column names on which the stored procedure sp_GetEmployees depends

sp_depends 'sp_GetEmployees'

Output : 

  
Sometime sp_depends does not report dependencies correctly. For example, at the moment we have Employees table and a stored procedure sp_GetEmployees. 

Now drop the table Employees

Drop table Employees

and then recreate the table again

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Now execute the following, to find the objects that depend on Employees table

sp_depends 'Employees'

We know that stored procedure sp_GetEmployees still depends on Employees table. But sp_depends does not report this dependency, as the Employees table is dropped and recreated.
Object does not reference any object, and no objects reference it.

sp_depends is on the deprecation path. This might be removed from the future versions of SQL server.




How to find object dependencies using the following dynamic management functions

• sys.dm_sql_referencing_entities
• sys.dm_sql_referenced_entities
• Difference between 
• Referencing entity and Referenced entity
• Schema-bound dependency and Non-schema-bound dependency

The following example returns all the objects that depend on Employees table. 
Select * from sys.dm_sql_referencing_entities('dbo.Employees','Object')

Difference between referencing entity and referenced entity
A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which is appearing inside the SQL expression is known as referenced entity and the object which has the SQL expression is known as a referencing entity.

To get the REFERENCING ENTITIES use SYS.DM_SQL_REFERENCING_ENTITIES dynamic management function

To get the REFERENCED ENTITIES use SYS.DM_SQL_REFERENCED_ENTITIES dynamic management function

Now, let us say we have a stored procedure and we want to find the all objects that this stored procedure depends on. This can be very achieved using another dynamic management function, sys.dm_sql_referenced_entities.  

The following query returns all the referenced entities of the stored procedure sp_GetEmployeesandDepartments

Select * from

sys.dm_sql_referenced_entities('dbo.sp_GetEmployeesandDepartments','Object')

Please note : For both these dynamic management functions to work we need to specify the schema name as well. Without the schema name you may not get any results.

Difference between Schema-bound dependency and Non-schema-bound dependency
Schema-bound dependency : Schema-bound dependency prevents referenced objects from being dropped or modified as long as the referencing object exists

Example : A view created with SCHEMABINDING, or a table created with foreign key constraint.

Non-schema-bound dependency : A non-schema-bound dependency doesn't prevent the referenced object from being dropped or modified.

Calculate running total in SQL Server 2012

We will use the following Employees table for the examples in this video.


SQL Script to create Employees table
Create Table Employees

(

     Id int primary key,

     Name nvarchar(50),

     Gender nvarchar(10),

     Salary int

)

Go

Insert Into Employees Values (1, 'Mark', 'Male', 5000)

Insert Into Employees Values (2, 'John', 'Male', 4500)

Insert Into Employees Values (3, 'Pam', 'Female', 5500)

Insert Into Employees Values (4, 'Sara', 'Female', 4000)

Insert Into Employees Values (5, 'Todd', 'Male', 3500)

Insert Into Employees Values (6, 'Mary', 'Female', 5000)

Insert Into Employees Values (7, 'Ben', 'Male', 6500)

Insert Into Employees Values (8, 'Jodi', 'Female', 7000)

Insert Into Employees Values (9, 'Tom', 'Male', 5500)

Insert Into Employees Values (10, 'Ron', 'Male', 5000)

Go

SQL Query to compute running total without partitions

SELECT Name, Gender, Salary,

        SUM(Salary) OVER (ORDER BY ID) AS RunningTotal

FROM Employees



SQL Query to compute running total with partitions
SELECT Name, Gender, Salary,

        SUM(Salary) OVER (PARTITION BY Gender ORDER BY ID) AS RunningTotal

FROM Employees



What happens if I use order by on Salary column
If you have duplicate values in the Salary column, all the duplicate values will be added to the running total at once. In the example below notice that we have 5000 repeated 3 times. So 15000 (i.e 5000 + 5000 + 5000) is added to the running total at once. 

SELECT Name, Gender, Salary,

        SUM(Salary) OVER (ORDER BY Salary) AS RunningTotal

FROM Employees



So when computing running total, it is better to use a column that has unique data in the ORDER BY clause.