CASE Expression in WHERE Clause

Posted on by madhivanan

Pinal Dave, Blogger and Speaker, posted about How to Write Case Statement in WHERE Clause? in which he showed a method to skip comparison of value if the value of parameter is empty.

Here is another method

Let us create the same data structure

USE tempdb
GO
CREATE TABLE Contacts
(
ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100)
)
INSERT INTO Contacts (ID, FirstName, LastName)
SELECT 1, 'Pinal', 'Dave'
UNION ALL
SELECT 2, 'Mark', 'Smith'
UNION ALL
SELECT 3, 'Mohan', 'Shah'
UNION ALL
SELECT 4, 'Matt', 'Alexus'
UNION ALL
SELECT 5, 'Roger', 'Croc'
GO

SELECT FirstName, LastName
FROM Contacts
GO

Now the search condition is that first name and last name values are passed. If either of them is empty it should skip the condition. Pinal already showed a method. Here is another method that does the same job

DECLARE @FirstName VARCHAR(100)
SET @FirstName = ''

DECLARE @LastName VARCHAR(100)
SET @LastName = 'Dave' 

-- Pinal's method
SELECT FirstName, LastName
FROM Contacts
WHERE
    FirstName = CASE
    WHEN LEN(@FirstName) > 0 THEN  @FirstName
    ELSE FirstName
    END
AND
    LastName = CASE
    WHEN LEN(@LastName) > 0 THEN  @LastName
    ELSE LastName
    END

-- Alternate method
SELECT FirstName, LastName
FROM Contacts
WHERE
    (FirstName = @FirstName) or (@FirstName='')
	AND
	(LastName = @LastName) or (@LastName='')

Both return the following result

FirstName                     LastName
----------------------------- -----------------------
Pinal                         Dave

How to search specific value in character column of all tables?

Posted on by madhivanan

Few questions are asked in forums about searching a specific value across all tables in the database stored in character columns (char, varchar, etc)

There can be several methods to make this possible. Here is one of them

Let us create this stored procedure

create procedure search_all_tables 
(
	@seach_string varchar(max)
)
as
declare @sql varchar(max)
set @sql=''

select 
   @sql=@sql+'select '''+c.table_name+''' as table_name,
		'''+c.column_name+''' as column_name,
		['+c.column_name+'] as column_value 
from '+c.table_name+' 
where 
	['+c.column_name+'] like N''%'+@seach_string+'%''
	 union all ' 
from 
        information_schema.columns as c 
	inner join information_schema.tables as t 
	on c.table_name=t.table_name 
where 
        t.table_type='BASE TABLE' and 
        data_type like '%char%' 
set @sql=left(@sql,len(@sql)-10)
exec(@sql)

Also create few tables

create table test1 (col1 int, col2 varchar(100))
insert into test1 
select 1,'welcome to sql server'
GO
create table test2 (col1 int, col2 varchar(max))
insert into test2 
select 10,'welcome to sql server'
GO
create table test3 
(
col1 int, 
col2 varchar(max),
col3 varchar(200)
)
insert into test3
select 10,'welcome to database programming','sql version 2012'
union all
select 11,'welcome',''

GO
create table test4 (col1 int, col2 varchar(max))
insert into test4
select 22,'Unix shell programming'

Suppose you want to find out the table name, column name and string value that contains the value ‘sql’, you can execute the procedure as shown below

EXECUTE search_all_tables 'sql'

The result is

table_name column_name column_value
---------- ----------- -------------------------
test1      col2        welcome to sql server
test2      col2        welcome to sql server
test3      col3        sql version 2012

It listed out all the column names where string value sql is part of it.

Suppose you want to find out the table name, column name and string value that contains the value ‘programming’, execute the following 

EXECUTE search_all_tables 'programming'

The result is

table_name column_name column_value
---------- ----------- --------------------------------
test4      col2        Unix shell programming
test3      col2        welcome to database programming

This way you can make use of this stored procedure to search for a character values stored in character columns of any user defined tables in the entire database.

When is usage of semicolon necessary?

Posted on by madhivanan

Semicolon is used as a statement terminator in almost all RDBMSs. However the usage of semicolon is optional in SQL Server except for few cases. Here are at least three cases where usage of semicolon is a must

1 At the start of the Common Table Expression (If any statement precedes it)

2 If the procedure name is GO ,to execute it use at the end of GO

Let us create this stored procedure with Batch separator GO

use tempdb

GO

CREATE PROCEDURE GO
as
select 10 as number

Now execute the procedure

GO

Nothing happens

Now execute it with semicolon

GO;

The result is 10

3 At the end of Merge statement

Let me know if it is required in any other cases.