What do you think the result of the following SELECT statement?
select 10.*10.[100]
Answers
1 100000 2 Incorrect syntax near '[100] 3 100 4 10
Advertisements
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
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
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
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
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
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
Method 2 Highlight the table name and press ALT+F1
#testing
The result is as shown below
SQL Server provides various ways to retrieve column information for a table
