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.

How to Manually Attach Databases in Management Studio

In addition to the ability to integrate into the HELM Control Panel for the creation of new databases, Microsoft SQL Server provides users the capability to directly attach existing databases to an SQL instance with the use of SQL Server Management Studio.

Step 1

First, we will need to locate the databases files to attach by using Windows Explorer to navigate to them. In this case, we are attaching a database named, ‘example_db‘, located in the folder, ‘C:\Database‘, seen below.

Step 2

As you can see, there are two files which drive an SQL database: the ‘.MDF‘ file which contains the primary data, and the ‘.LDF‘ file which logs all transactions related to the database. Both files are necessary for the database to function correctly.

Now, we need to place them in the appropriate folder containing the other SQL databases on the server. Typically, this folder will be, ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data‘, however, depending on the data directory specified during installation, the database files may be in a different location.

It is not a requirement that all database files be kept in the same location, as they technically can be attached from any location on the server, however for the sake of organization, we will place the files in the specified data folder. Start by highlighting both files, right-clicking on them and choosing ‘Copy‘.

Step 3

Next, using Windows Explorer, navigate to the data directory specified above, and paste the files into them by right-clicking anywhere in the empty space within the folder, and choosing ‘Paste‘.

Step 4

You will now see the database files have been pasted into the folder, located beneath the existing databases files.

Having Trouble Implementing This Solution?

Why not let our Microsoft^® Certified Engineers do it for you.
Just $50.00 one time fee.

Request Immediate Expert Help 

Step 5

Now, we are ready to attach them. Open SQL Server Management Studio from the Start Menu. If the icon is not in the list of recently used programs, you can navigate to ‘All Programs –> Microsoft SQL Server 2005 –> SQL Server Management Studio’.

Step 6

Log in to Management Studio using your server’s name and Windows Authentication, and pressing ‘Connect‘.

Step 7

Once logged in, click the plus sign next to the ‘Databases‘ heading to see a list of existing databases within the SQL instance.

Step 8

Right-click on ‘Databases‘, and choose ‘Attach‘ from the list of available options.

Step 9

This will bring up the Attach Databases window. Click the button which says ‘Add‘.

Step 10

You should now be able to see a list of existing databases on the server, as Management Studio will default to the data directory mentioned above. Click on the database you wish to attach, ‘example_db.mdf‘, in this case, and click OK.

Step 11

Back at the Attach Databases window, it will confirm the locations of the primary data and transaction log files. ClickOK.

Step 12

As you can see, the database is now attached to the SQL instance.

Step 13

Now, we need to create a user for the database to allow for external connectivity. Click the plus sign next to ‘Security‘, right-click the ‘Logins‘ folder and click ‘New Login‘.

Step 14

There are many options on the New Login windows which need to be set for the database user to be created successfully:

• Enter your desired name for the database user in the Login Name field.
• Set the user to use SQL Authentication, to allow for external connectivity.
• Enter in and confirm your desired password.
• Uncheck the option for ‘Enforce password expiration‘, which will in turn disable the option for ‘User must change password at next login‘.
• Next to the ‘Default Database‘ drop-down menu, select the manually attached database.

Step 15

Once your desired options are set, click the option in the left-hand menu which says ‘User Mapping‘.

Step 16

In the User Mapping windows, there are two options which need to be set. The first is to place a check-mark next to the manually attached database in the ‘Map‘ column. Then, in the list of Database Membership Roles, place a check mark next to the ‘db_owner‘ role. Click OK once the options are in place.

Step 17

The newly created user can now be seen in the list of all SQL users within the instance.

You have now manually attached an SQL database using Management Studio. If you have any questions or concerns regarding this procedure, feel free to contact our Technical Support Department for assistance.