THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

Dynamic SQL and Late Binding

We all know that dynamic SQL runs in its own scope (see here for details), and that if used improperly it can have serious security implications.  But it's also important to realize that dynamic SQL evaluates your SQL statements more like a stored procedure than a regular script.  In stored procedures, if the tables you reference exist the stored proc must reference the table structures as they exist at stored proc creation time.  That is to say stored procs will late bind to referenced tables only if the tables do not exist at stored proc creation time; dynamic SQL does the same.  If the table already exists, SQL Server validates the structure at parse time.

Consider the following SQL code.  In this code we create a table named xyz in one batch.  In the subsequent batch we check for the existence of table xyz, drop it if it already exists, and then recreate it.  In this scenario table xyz starts with two columns named i and c; when I rebuild the table in the second batch I will change the table structure to 3 columns--i, c and x:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

CREATE TABLE dbo.xyz
(
 
i int,
 
c varchar(100)
);
GO

IF
OBJECT_ID('dbo.xyz') IS NOT NULL
  DROP TABLE dbo.xyz;

CREATE
TABLE dbo.xyz
(
  i int,
 
c varchar(100),
 
x varchar(100)
);

INSERT INTO dbo.xyz
(
  i,
  c,
 
x
)
VALUES
(
  1,
  'hello',
  'good bye'
);
GO

DROP
TABLE dbo.xyz;
GO

This code runs successfully.  Now let's convert the second batch to dynamic SQL and execute the sample again:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

CREATE
TABLE dbo.xyz
(
  i int,
  c varchar(100)
);
GO

DECLARE
@sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
  DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
  i int,
  c varchar(100),
  x varchar(100)
);

INSERT INTO dbo.xyz
(
  i,
  c,
  x
)
VALUES
(
  1,
  ''hello'',
  ''good bye''
);'
;

EXEC
(@sql);
GO

DROP
TABLE dbo.xyz;
GO

The dynamic SQL example returns the following error message:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'x'
.

Why is this?  Well the xyz table exists, but the column x referenced in the dynamic SQL INSERT does not exist at parse time.  It will exist after the CREATE TABLE statement is executed, but the INSERT statement parse errors out before the dynamic SQL is executed.  If you comment out the final DROP TABLE statement you can see that the DROP TABLE and CREATE TABLE statements in the dynamic SQL were not executed.  If you're doing something like this with dynamic SQL you can ensure that the DROP TABLE and CREATE TABLE are executed before the INSERT statement is parsed by breaking up your dynamic SQL like below:

DECLARE @sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
  DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
  i int,
  c varchar(100),
  x varchar(100)
);'
;

EXEC (@sql);

SET
@sql = N'
INSERT INTO dbo.xyz
(
  i,
  c,
  x
)
VALUES
(
  1,
  ''hello'',
  ''good bye''
);'
;

EXEC
(@sql);
GO

As an interesting (if not entirely useful) aside, if you create the three column table above, insert data into it, then drop it and recreate with two columns, the dynamic SQL succeeds.  It appears to remember the old table structure and runs just fine.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

CREATE
TABLE dbo.xyz
(
  i int,
  c varchar(100),
  x varchar(100)
);
GO

INSERT
INTO dbo.xyz
(
  i,
  c
,
  x
)
VALUES
(
  1,
  'a',
  'b'
);
GO

IF
OBJECT_ID('dbo.xyz') IS NOT NULL
  DROP TABLE dbo.xyz;
GO

CREATE
TABLE dbo.xyz
(
  i int,
  c varchar(100)
);
GO

DECLARE
@sql nvarchar(1000);
SET @sql = N'
IF OBJECT_ID(''dbo.xyz'') IS NOT NULL
  DROP TABLE dbo.xyz;

CREATE TABLE dbo.xyz
(
  i int,
  c varchar(100),
  x varchar(100)
);

INSERT INTO dbo.xyz
(
  i,
  c,
  x
)
VALUES
(
  1,
  ''hello'',
  ''good bye''
);'
;

EXEC (@sql);
GO

DROP
TABLE dbo.xyz;
GO

The trick is to remember that if the table already exists, dynamic SQL is looking at your table structure at parse time not at run time.  So your target table structure has to fit the dynamic SQL statements that are being parsed.

Published Wednesday, June 23, 2010 1:54 PM by Mike C

Comments

 

Uri Dimant said:

Very good points Michael, thanks

June 24, 2010 1:23 AM
 

Uri Dimant said:

Very good points Michael, thanks

June 24, 2010 1:23 AM
 

Linchi Shea said:

I was reading Michael Cole’s post on Dynamic SQL and Late Binding , and it reminded me of an issue I

July 15, 2010 5:45 PM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement