Passing WildCard inside a parameter in Stored Procedure

While writing a stored procedure, sometime we are asked to return all the row if we do not provide a parameter.

For example.

Let's say we have a table called [State] which hold values like this.

ID StateFullName StateCode
1 Alaska   AK
2. Alabama AL
.
.
.
.
.
and like


So we want to write a stored procedure where sometime we are providing code to get state full name and sometime not.

In that case our SP will be something like this.

Create Proc as usp_getStateCode
(@statecode varchar(10) = NULL)
AS
Select
StateFullName, StateCode
From dbo.[State]
Where stateCode = ISNULL(@statecode,StateCode)


Now let's say we have a situation where we want to find a name of database and we do not know fullname of database.

Let's say we have a table which list all my database name and instance name.

DatabaseInstance table with following structure
(instancename varchar(128)
databasename varchar(128)


Now we want to write a stored procedure which should give me all the values if I am not passing any value or if I am passing a values which is not complete, it should compare with wildcard match and return me those matching values.


Create Proc as usp_getDatebaseName
(@DatabaseName varchar(128) = NULL
AS
Select
InstanceName, DatabaseName
From DatabaseInstance
Where DatabaseName Like ISNULL('%' + @databaseName + '%',DatabaseName)


This stored procedure has 70% cost as it is doing full scan on table for database name as we are passing wildcard

This stored procedure will return all the values if none is provided and it will return those matching values if provided.

Another way to write this is:

Create  PROCEDURE [dbo].[usp_getDatebaseName]
    @databaseName VARCHAR(128)  = NULL
AS
IF @databasename <> ''
SET @databaseName = '%' + @databaseName + '%'

IF @databaseName IS NULL
SET @databasename = '%'
Select
InstanceName, DatabaseName
From DatabaseInstance
Where DatabaseName Like ISNULL(@databaseName,DatabaseName)

This stored procedure take 30% scan as it is doing seek scan.