Dynamic sql execution – EXEC vs sp_executesql

The only way of executing dynamic sql is thru the usage of methods EXEC and sp_executesql. In this post we will explore the differences between the two.

 

EXEC is not parameterised

EXEC(‘select 1’)

SP_executesql is parameterised

EXEC sp_executesql N'select 1'

Let us create this simple data set

use tempdb

create table customer (cust_id int, cust_name varchar(100))
insert into customer(cust_id,cust_name)
select 1,'testing'

Now assume that you want to pass table name, column name and value for the column to get the data

It can be done in dynamic sql in the following methods

Method 1 EXEC

Declare @sql varchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
Set @sql='select * from '+@table_name+' where '+@col_name+' = '+cast(@col_value as varchar(10))
Exec(@sql)

The above method is not parameterised and the sql query is constructed and executed using EXEC

Method 2 sp_executesql


Declare @sql nvarchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
set @sql='select * from '+ @table_name +' where '+@col_name+'=@col_value'
exec sp_executesql @sql,N' @col_value int',@col_value 

The above method is parameterised and the sql query is constructed and executed via parameter using sp_executesql

Both of them produce the following result

cust_id     cust_name
----------- ----------------
1           testing

In the next post we will explore SQL Injection

Advertisements

Scripting out Data from a table as INSERT statements

Everyone know how to script out a definition of a table. However many do not aware that you can script out data as well in the form of SELECT statements

This option is available only from SQL Server Management Studio (SSMS). 

Here is what you need to do

Right click on database

–>tasks–>Generate scripts–>click next–>Choose specific database_objects

Now check out the tables you want to script data out

Choose one of the saving options (file, clipboard,etc).

Click Advanced and in the drop down for label Type of data to script , choose the value Data only

Click next. You get all the data in the form of SELECT statements

Here are the pictures for easy understanding

 

 

Dynamic Crosstab with multiple PIVOT columns – Part 2

In Part 1 of this post, I showed a method of producing crosstab results using two PIVOT columns. In this post, I will show you the method of how you can change the grouping columns dynamically.

I am using the same data set created in Part 1. In Part 1, the grouping column was Last name of the employee. Basically it showed the total orders handled by each employee. Now in this post, we will see how to change Last name into some other column.

Let us use Shipping company’s name as grouping column. Executing the following code

EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shipcity FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcity,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot3

Let us use Shipping Postal code as grouping column. Executing the following code


EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shippostalcode FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shippostalcode,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot4

Now, as you see, this stored procedure is very dynamic and will work for any dataset (any tables, views, etc). All you need to do is effectively use this procedure and pass the relevant names for aggregation , grouping and PIVOT columns

Dynamic Crosstab with multiple PIVOT columns

Most of you know how to do crosstab result using a single PIVOT column. Here I explain how you can do the crosstab result with multiple PIVOT columns.

I am using the dataset from the famous old northwind database

Let us create these two tables

USE [tempdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
	[Extension] [nvarchar](4) NULL,
	[Notes] [ntext] NULL,
	[ReportsTo] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [nchar](5) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[RequiredDate] [datetime] NULL,
	[ShippedDate] [datetime] NULL,
	[ShipVia] [int] NULL,
	[Freight] [money] NULL,
	[ShipName] [nvarchar](40) NULL,
	[ShipAddress] [nvarchar](60) NULL,
	[ShipCity] [nvarchar](15) NULL,
	[ShipRegion] [nvarchar](15) NULL,
	[ShipPostalCode] [nvarchar](10) NULL,
	[ShipCountry] [nvarchar](15) NULL
) ON [PRIMARY]

GO

Let us populate some sample data


USE [tempdb]
GO
-- Add data to table employees

SET IDENTITY_INSERT [dbo].[employees] ON 

GO
INSERT [dbo].[employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(N'1948-12-08T00:00:00.000' AS DateTime), CAST(N'1992-05-01T00:00:00.000' AS DateTime), N'507 - 20th Ave. E.
Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857', N'5467', N'Education includes a BA in psychology from Colorado State University in 1970.  She also completed "The Art of the Cold Call."  Nancy is a member of Toastmasters International.', 2)
GO
INSERT [dbo].[employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales', N'Dr.', CAST(N'1952-02-19T00:00:00.000' AS DateTime), CAST(N'1992-08-14T00:00:00.000' AS DateTime), N'908 W. Capital Way', N'Tacoma', N'WA', N'98401', N'USA', N'(206) 555-9482', N'3457', N'Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.', NULL)
GO
INSERT [dbo].[employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative', N'Ms.', CAST(N'1963-08-30T00:00:00.000' AS DateTime), CAST(N'1992-04-01T00:00:00.000' AS DateTime), N'722 Moss Bay Blvd.', N'Kirkland', N'WA', N'98033', N'USA', N'(206) 555-3412', N'3355', N'Janet has a BS degree in chemistry from Boston College (1984).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.', 2)
GO
INSERT [dbo].[employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative', N'Mrs.', CAST(N'1937-09-19T00:00:00.000' AS DateTime), CAST(N'1993-05-03T00:00:00.000' AS DateTime), N'4110 Old Redmond Rd.', N'Redmond', N'WA', N'98052', N'USA', N'(206) 555-8122', N'5176', N'Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966).  She was assigned to the London office temporarily from July through November 1992.', 2)
GO
INSERT [dbo].[employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager', N'Mr.', CAST(N'1955-03-04T00:00:00.000' AS DateTime), CAST(N'1993-10-17T00:00:00.000' AS DateTime), N'14 Garrett Hill', N'London', NULL, N'SW1 8JR', N'UK', N'(71) 555-4848', N'3453', N'Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976.  Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.', 2)
GO
SET IDENTITY_INSERT [dbo].[employees] OFF
GO

-- Add data to table orders 

SET IDENTITY_INSERT [dbo].[orders] ON 

GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10248, N'VINET', 5, CAST(N'1996-07-04T00:00:00.000' AS DateTime), CAST(N'1996-08-01T00:00:00.000' AS DateTime), CAST(N'1996-07-16T00:00:00.000' AS DateTime), 3, 32.3800, N'Vins et alcools Chevalier', N'59 rue de l''Abbaye', N'Reims', NULL, N'51100', N'France')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10250, N'HANAR', 4, CAST(N'1996-07-08T00:00:00.000' AS DateTime), CAST(N'1996-08-05T00:00:00.000' AS DateTime), CAST(N'1996-07-12T00:00:00.000' AS DateTime), 2, 65.8300, N'Hanari Carnes', N'Rua do Paço, 67', N'Rio de Janeiro', N'RJ', N'05454-876', N'Brazil')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10251, N'VICTE', 3, CAST(N'1996-07-08T00:00:00.000' AS DateTime), CAST(N'1996-08-05T00:00:00.000' AS DateTime), CAST(N'1996-07-15T00:00:00.000' AS DateTime), 1, 41.3400, N'Victuailles en stock', N'2, rue du Commerce', N'Lyon', NULL, N'69004', N'France')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10252, N'SUPRD', 4, CAST(N'1996-07-09T00:00:00.000' AS DateTime), CAST(N'1996-08-06T00:00:00.000' AS DateTime), CAST(N'1996-07-11T00:00:00.000' AS DateTime), 2, 51.3000, N'Suprêmes délices', N'Boulevard Tirou, 255', N'Charleroi', NULL, N'B-6000', N'Belgium')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10253, N'HANAR', 3, CAST(N'1996-07-10T00:00:00.000' AS DateTime), CAST(N'1996-07-24T00:00:00.000' AS DateTime), CAST(N'1996-07-16T00:00:00.000' AS DateTime), 2, 58.1700, N'Hanari Carnes', N'Rua do Paço, 67', N'Rio de Janeiro', N'RJ', N'05454-876', N'Brazil')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10254, N'CHOPS', 5, CAST(N'1996-07-11T00:00:00.000' AS DateTime), CAST(N'1996-08-08T00:00:00.000' AS DateTime), CAST(N'1996-07-23T00:00:00.000' AS DateTime), 2, 22.9800, N'Chop-suey Chinese', N'Hauptstr. 31', N'Bern', NULL, N'3012', N'Switzerland')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10256, N'WELLI', 3, CAST(N'1996-07-15T00:00:00.000' AS DateTime), CAST(N'1996-08-12T00:00:00.000' AS DateTime), CAST(N'1996-07-17T00:00:00.000' AS DateTime), 2, 13.9700, N'Wellington Importadora', N'Rua do Mercado, 12', N'Resende', N'SP', N'08737-363', N'Brazil')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10257, N'HILAA', 4, CAST(N'1996-07-16T00:00:00.000' AS DateTime), CAST(N'1996-08-13T00:00:00.000' AS DateTime), CAST(N'1996-07-22T00:00:00.000' AS DateTime), 3, 81.9100, N'HILARION-Abastos', N'Carrera 22 con Ave. Carlos Soublette #8-35', N'San Cristóbal', N'Táchira', N'5022', N'Venezuela')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10258, N'ERNSH', 1, CAST(N'1996-07-17T00:00:00.000' AS DateTime), CAST(N'1996-08-14T00:00:00.000' AS DateTime), CAST(N'1996-07-23T00:00:00.000' AS DateTime), 1, 140.5100, N'Ernst Handel', N'Kirchgasse 6', N'Graz', NULL, N'8010', N'Austria')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10259, N'CENTC', 4, CAST(N'1996-07-18T00:00:00.000' AS DateTime), CAST(N'1996-08-15T00:00:00.000' AS DateTime), CAST(N'1996-07-25T00:00:00.000' AS DateTime), 3, 3.2500, N'Centro comercial Moctezuma', N'Sierras de Granada 9993', N'México D.F.', NULL, N'05022', N'Mexico')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10260, N'OTTIK', 4, CAST(N'1996-07-19T00:00:00.000' AS DateTime), CAST(N'1996-08-16T00:00:00.000' AS DateTime), CAST(N'1996-07-29T00:00:00.000' AS DateTime), 1, 55.0900, N'Ottilies Käseladen', N'Mehrheimerstr. 369', N'Köln', NULL, N'50739', N'Germany')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10261, N'QUEDE', 4, CAST(N'1997-07-19T00:00:00.000' AS DateTime), CAST(N'1996-08-16T00:00:00.000' AS DateTime), CAST(N'1996-07-30T00:00:00.000' AS DateTime), 2, 3.0500, N'Que Delícia', N'Rua da Panificadora, 12', N'Rio de Janeiro', N'RJ', N'02389-673', N'Brazil')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10265, N'BLONP', 2, CAST(N'1996-07-25T00:00:00.000' AS DateTime), CAST(N'1996-08-22T00:00:00.000' AS DateTime), CAST(N'1996-08-12T00:00:00.000' AS DateTime), 1, 55.2800, N'Blondel père et fils', N'24, place Kléber', N'Strasbourg', NULL, N'67000', N'France')
GO
INSERT [dbo].[orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10266, N'WARTH', 3, CAST(N'1997-07-26T00:00:00.000' AS DateTime), CAST(N'1996-09-06T00:00:00.000' AS DateTime), CAST(N'1996-07-31T00:00:00.000' AS DateTime), 3, 25.7300, N'Wartian Herkku', N'Torikatu 38', N'Oulu', NULL, N'90110', N'Finland')
GO
SET IDENTITY_INSERT [dbo].[orders] OFF
GO

Now create the following stored procedure

CREATE procedure CrossTab_multiple_columns
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100) = Null
)
AS
set nocount on
set ansi_warnings off
declare @sql varchar(8000)
Select @sql = ''
Select @OtherCols= isNull(', ' + @OtherCols,'')
create table #pivot_columns (pivot_column_name varchar(100))
Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+''''
insert into #pivot_columns
exec(@sql)
select @sql=''
create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100))
Select @PivotCol=''
Select @PivotCol=min(pivot_column_name) from #pivot_columns
While @PivotCol>''
Begin
    insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
    exec
    (
    'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data    from
    ('+
        @select
    +'
    ) T'
    )
    Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end
select
    @sql = @sql + ', ' +
    replace(
        replace(
                @Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
                pivot_column_data + ''' THEN '
                    ),
            ')[', ' END) as [' + pivot_column_data
                )
from #pivot_columns_data
order by pivot_column_name
exec
(
     'select ' + @GroupBy +@OtherCols +@sql +
    ' from (
    '+
        @select
    +'
    ) T
    GROUP BY ' + @GroupBy
)
drop table #pivot_columns
drop table #pivot_columns_data
set nocount off
set ansi_warnings on

GO

Suppose you want to find total orders handled by each employee for each country and year in separate columns, you can use the following code to get that result

EXEC CrossTab_multiple_columns
'SELECT LastName, OrderDate,shipcountry FROM northwind..Employees Employees
INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcountry,Year(OrderDate)',
'Count(LastName)[]',
'LastName'

The result is as shown below

pivot1

As you see it lists out the total orders for each employee across various countries and year

Executing the following code will give you the orders done in city and year

EXEC CrossTab_multiple_columns
'SELECT LastName, OrderDate,shipcity FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcity,Year(OrderDate)',
'Count(LastName)[]',
'LastName'

The result is

pivot2

This way you can effectively make use of this stored procedure to derive cross tab reports with multiple PIVOT columns

How to Get Column Names From a Specific Table?

Pinal Dave, blogger and speaker, posted about SQL Server – How to Get Column Names From a Specific Table? where he showed two methods to get column names for a specific table.

There are two other simple methods to do the same

Let us create this table

use tempdb

create table #testing(id int, names varchar(100))

Method 1 Use System stored procedure sp_columns

EXEC sp_columns  '#testing'

The result is as shown below

col1

Method 2 Highlight the table name and press ALT+F1

#testing

The result is as shown below

col2

SQL Server provides various ways to retrieve column information for a table

CASE Expression in WHERE Clause

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?

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?

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.