Dynamic SQL and Late Binding

Mike C — Wed, 23 Jun 2010 16:54:00 GMT

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.

Search results matching tags 'T-SQL' and 'dynamic sql'

Dynamic SQL and Late Binding