sp_executesql Usage

Rubens

unread,

Mar 29, 2008, 4:56:00 PM3/29/08

to

I am trying to create a simple proc using sp_executesql and I can't seem to get the syntax correct. I am thinking it has something to do with the single quotes I have in my exec sp_executesql N' statement, so I've tried different combinations of single quotes to no avail.

Can someone please tell me what I am doing wrong?

Thank-you,

Rubens

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- Dynamic SQL Using: sp_executesql

use

AdventureWorks

go

-- Create proc

create

proc dbo.proc_DynamicSQL_Using_sp_executesql

(

@DatabaseName

varchar(30),

@SchemaName

varchar(30),

@TableName

varchar(30),

@ColumnName

varchar(30),

@Value

int

)

as

exec

sp_executesql N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'

+ @TableName + ' where ' + @ColumnName + ' = ' + @Value

return

go

-- Execute proc

exec

proc_DynamicSQL_Using_sp_executesql 'AdventureWorks', 'Person', 'Contact', 'ContactID', 1

-- Execute proc specifying parameter names

exec

proc_DynamicSQL_Using_sp_executesql

@DatabaseName

= 'AdventureWorks',

@SchemaName

= 'Person',

@TableName

= 'Contact',

@ColumnName

= 'ContactID',

@Value

= 1

go

-- This is the select statement generated

--select * from AdventureWorks.Person.Contact where ContactID = 1

Erland Sommarskog

unread,

Mar 29, 2008, 7:56:32 PM3/29/08

to

Rubens (ruben...@hotmail.com) writes:
> I am trying to create a simple proc using sp_executesql and I can't seem
> to get the syntax correct. I am thinking it has something to do with
> the single quotes I have in my exec sp_executesql N' statement, so I've
> tried different combinations of single quotes to no avail.
>
> Can someone please tell me what I am doing wrong?

>

> exec sp_executesql N'select * from ' + @DatabaseName + '.' + @SchemaName +
> '.' + @TableName + ' where ' + @ColumnName + ' = ' + @Value

In difference to many other languages, T-SQL does not accept expression
for parameters; it only accepts constants and variables. Thus, you need
do somthing like:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'

+ @TableName + ' where ' + @ColumnName + ' = ' + @Value

EXEC sp_executesql @sql

I like to add that this test procedure you are writing is fairly
meaningless. In a well-designed database, there is rarely reason
to pass table names as parameters.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Plamen Ratchev

unread,

Mar 29, 2008, 8:00:52 PM3/29/08

to

Declare a string variable to construct the SQL and then pass it as parameter
to sp_executesql:

CREATE PROCEDURE dbo.proc_DynamicSQL_Using_sp_executesql

(
@DatabaseName varchar(30),
@SchemaName varchar(30),
@TableName varchar(30),
@ColumnName varchar(30),
@Value int
)

AS

DECLARE @sql NVARCHAR(500)

SET @sql = N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'
+ @TableName + ' where ' + @ColumnName + ' = ' + CAST(@Value AS VARCHAR(10))

EXEC sp_executesql @sql

A better approach would be to pass the value as parameter to sp_executesql:

CREATE PROCEDURE dbo.proc_DynamicSQL_Using_sp_executesql

(
@DatabaseName varchar(30),
@SchemaName varchar(30),
@TableName varchar(30),
@ColumnName varchar(30),
@Value int
)

AS

DECLARE @sql NVARCHAR(500)

SET @sql = N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'
+ @TableName + ' where ' + @ColumnName + ' = @param_value'

EXEC sp_executesql @sql,
@params = N'@param_value INT',
@param_value = @value

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Rubens

unread,

Mar 30, 2008, 10:46:51 AM3/30/08

to

Thank-you both, this worked like a charm.

Erland, I appreciate your comments about it being a fairly meaningless
query. I think I was trying more than anything to get familiar with using
dynamic SQL, and in the meantime was creating something that would never be
used! I changed it to just pass the value, which makes a little more sense.

Thanks again,
Rubens

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:0931E68D-C1DE-4160...@microsoft.com...

Stuart Ainsworth

unread,

Mar 30, 2008, 8:05:10 PM3/30/08

to

> >http://www.SQLStudio.com- Hide quoted text -
>
> - Show quoted text -

One style trick I've started using recently when building dynamic SQL
is to delimit the replacement parts with pipe characters, and then do
a replace; it just is easier to read (IMO) than trying to break out of
the string, concatenate, add it back in, etc..

For example

SET @sql = N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'
+ @TableName + ' where ' + @ColumnName + ' = @param_value'

would become

It does involve a lot more keystrokes, but it keeps me from doing
stupid things like forgetting to leave a space before and after part
of the string (like the + ' where ' + fragment in Plamen's
example).

Stu

Rubens

unread,

Mar 31, 2008, 8:00:59 AM3/31/08

to

That's an interesting little trick and its definitely more legible in to me
also. Do you know if something like this has any noticeable impact on
performance?

Thank-you,
Rubens

"Stuart Ainsworth" <stuart.a...@gmail.com> wrote in message
news:28269475-4de2-4c28...@p25g2000hsf.googlegroups.com...

Stuart Ainsworth

unread,

Mar 31, 2008, 8:56:13 AM3/31/08

to

On Mar 31, 8:00 am, "Rubens" <rubensr...@hotmail.com> wrote:
> That's an interesting little trick and its definitely more legible in to me
> also. Do you know if something like this has any noticeable impact on
> performance?
>
> Thank-you,
> Rubens
>

> "Stuart Ainsworth" <stuart.ainswo...@gmail.com> wrote in message

> >> >http://www.SQLStudio.com-Hide quoted text -

>
> >> - Show quoted text -
>
> > One style trick I've started using recently when building dynamic SQL
> > is to delimit the replacement parts with pipe characters, and then do
> > a replace; it just is easier to read (IMO) than trying to break out of
> > the string, concatenate, add it back in, etc..
>
> > For example
>
> > SET @sql = N'select * from ' + @DatabaseName + '.' + @SchemaName + '.'
> > + @TableName + ' where ' + @ColumnName + ' = @param_value'
>
> > would become
>
> > SET @SQL = N'SELECT * FROM |DatabaseName|.|SchemaName|.|TableName|
> > WHERE |ColumnName| = @param_value'
>
> > SET @SQL = REPLACE(@SQL, '|DatabaseName|', @DatabaseName)
> > SET @SQL = REPLACE(@SQL, '|SchemaName|', @SchemaName)
> > SET @SQL = REPLACE(@SQL, '|TableName|', @TableName)
> > SET @SQL = REPLACE(@SQL, '|ColumnName|', @ColumnName)
>
> > It does involve a lot more keystrokes, but it keeps me from doing
> > stupid things like forgetting to leave a space before and after part
> > of the string (like the + ' where ' + fragment in Plamen's
> > example).
>

> > Stu- Hide quoted text -

>
> - Show quoted text -

I don't hink so; it's just manipulating the value of variable. It
should all be done in-memory.

Stu

Rubens

unread,

Mar 31, 2008, 9:52:33 AM3/31/08

to

Okay cool, thanks.

Rubens

"Stuart Ainsworth" <stuart.a...@gmail.com> wrote in message

news:5b036361-9847-4205...@b1g2000hsg.googlegroups.com...

Alex Kuznetsov

unread,

Mar 31, 2008, 11:57:15 AM3/31/08

to

On Mar 29, 6:56 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland,

Let me suggest a safer way, eliminating the risk of SQL injection:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'select * from ' + (SELECT [name] FROM sys.databases
WHERE [name]=@DatabaseName) + '.' +
(a subquery with @SchemaName) + '.'
+ (a subquery with @TableName) + ' where ' + (a subquery
with @ColumnName) + ' = ' + @Value
EXEC sp_executesql @sql

Erland Sommarskog

unread,

Mar 31, 2008, 5:45:51 PM3/31/08

to

A useful idea. With Alex's comment in mind, it's even better if you
do

SET @SQL = REPLACE(@SQL, '|DatabaseName|', quotename(@DatabaseName))

etc.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Plamen Ratchev

unread,

Mar 31, 2008, 6:02:05 PM3/31/08

to

I find it more natural to use the full parameter name:

SET @SQL = N'SELECT * FROM @DatabaseName.@SchemaName.@TableName'

Then the replace is more clear on what the goal is:

SET @SQL = REPLACE(@SQL, '@DatabaseName', quotename(@DatabaseName))

Plamen Ratchev
http://www.SQLStudio.com

Stuart Ainsworth

unread,

Mar 31, 2008, 6:59:31 PM3/31/08

to

First, the quotename thing is pretty cool; I'll have to start using
that.

Second, I wound up using pipe delimiters because it stood out in the
SQL statement when scanning it; since some of the dynamic SQL
statements I build use parameters passed from sp_executeSQL, the pipes
made it easy to tell which variables would need to be REPLACEd, and
which would have values passed it.

6 of one, half a dozen of the other, I suppose; just glad others find
it useful.

Stu

Rubens

unread,

Apr 1, 2008, 8:40:38 PM4/1/08

to

I've looked at the QUOTENAME function in BO and I'm not sure I understand
what it does, even after looking at the example. It says...

Returns a Unicode string with the delimiters added to make the input string
a valid Microsoft SQL Server 2005 delimited identifier.

Can someone please explain what this does in laytechie terms? And how it is
used in this example?

Thank-you,
Rubens

P.S. Stu, I will give you an example of where a "6 of one" = "half dozen"
of the other. Let's use a Baker's Dozen... and say, our SQL 6.5 HRIS
system! That system is indeed actually worth about 6.5 donuts. Thank
goodness it will be replaced this year... :-)

"Stuart Ainsworth" <stuart.a...@gmail.com> wrote in message

news:93eae442-c0dd-4580...@a23g2000hsc.googlegroups.com...

Aaron Bertrand [SQL Server MVP]

unread,

Apr 1, 2008, 9:51:18 PM4/1/08

to

Did you try

SELECT QUOTENAME('foo');

? All it does is put identifiers around the string passed in. So, foo
becomes [foo].

"Rubens" <ruben...@hotmail.com> wrote in message
news:ucwN1pFl...@TK2MSFTNGP06.phx.gbl...

Erland Sommarskog

unread,

Apr 2, 2008, 3:20:18 AM4/2/08

to

Rubens (ruben...@hotmail.com) writes:
> I've looked at the QUOTENAME function in BO and I'm not sure I understand
> what it does, even after looking at the example. It says...
>
> Returns a Unicode string with the delimiters added to make the input
> string a valid Microsoft SQL Server 2005 delimited identifier.
>
> Can someone please explain what this does in laytechie terms? And how
> it is used in this example?

quotename('Order Details') => [Order Details]
quotename('My [bracketed name]') => [My [bracketed name]]
quotename('O''Brien', '''') => 'O''Brien'

The purpose is to wrap the string in a delimiter (default [], and double any
closing delimiters within the string to make sure that the string works in
the context you use it.

In the examples in the thread we had quotename(@dbname) etc. This is because
if the database would be name My Space,

SELECT * FROM My Space.dbo.tbl

would not be valid SQL.

Alex Kuznetsov

unread,

Apr 2, 2008, 10:11:22 AM4/2/08

to

On Apr 1, 7:40 pm, "Rubens" <rubensr...@hotmail.com> wrote:
> I've looked at the QUOTENAME function in BO and I'm not sure I understand
> what it does, even after looking at the example. It says...
>
> Returns a Unicode string with the delimiters added to make the input string
> a valid Microsoft SQL Server 2005 delimited identifier.
>
> Can someone please explain what this does in laytechie terms? And how it is
> used in this example?
>
> Thank-you,
> Rubens
>
> P.S. Stu, I will give you an example of where a "6 of one" = "half dozen"
> of the other. Let's use a Baker's Dozen... and say, our SQL 6.5 HRIS
> system! That system is indeed actually worth about 6.5 donuts. Thank
> goodness it will be replaced this year... :-)
>

> "Stuart Ainsworth" <stuart.ainswo...@gmail.com> wrote in message

>
> news:93eae442-c0dd-4580...@a23g2000hsc.googlegroups.com...
>
> > On Mar 31, 6:02 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> >> I find it more natural to use the full parameter name:
>
> >> SET @SQL = N'SELECT * FROM @DatabaseName.@SchemaName.@TableName'
>
> >> Then the replace is more clear on what the goal is:
>
> >> SET @SQL = REPLACE(@SQL, '@DatabaseName', quotename(@DatabaseName))
>
> >> Plamen Ratchevhttp://www.SQLStudio.com
>
> > First, the quotename thing is pretty cool; I'll have to start using
> > that.
>
> > Second, I wound up using pipe delimiters because it stood out in the
> > SQL statement when scanning it; since some of the dynamic SQL
> > statements I build use parameters passed from sp_executeSQL, the pipes
> > made it easy to tell which variables would need to be REPLACEd, and
> > which would have values passed it.
>
> > 6 of one, half a dozen of the other, I suppose; just glad others find
> > it useful.
>
> > Stu

You can play with the following script and see how quotemane protects
your from injections:

declare @sql nvarchar(max),
@DatabaseName nvarchar(100),
@schemaName nvarchar(100),
@tableName nvarchar(100),
@columnName nvarchar(100)
select @DatabaseName = 'test.dbo.t;drop table t; select * from [test',
@schemaName = 'dbo',
@tableName = 't',
@columnName = 'c'

PRINT @sql

Also try setting

@DatabaseName = 'test].dbo.t;drop table t; select * from [test',

and see what happens

Rubens

unread,

Apr 4, 2008, 1:18:49 PM4/4/08

to

Sorry for the late reply on this, but oooooooooops. I shoulda tried that.
Thanks Aaron.

Rubens

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message
news:04ECA6FC-CBF4-453E...@microsoft.com...

Rubens

unread,

Apr 4, 2008, 1:19:49 PM4/4/08

to

Perfect, makes complete sense! Thanks Erland.

Rubens

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9A745EF16...@127.0.0.1...

Rubens

unread,

Apr 4, 2008, 1:36:42 PM4/4/08

to

Honestly, when I read stuff like this I'm amazed at just how great you guys
are to come up with code like this. This is a fantastic example and its
gonna take me a while to be able to think like this, but I'll try! So in
both examples, this SQL would never execute due to syntax errors.

Here is what each returned:

Example 1: select @DatabaseName = 'test.dbo.t;drop table t; select * from
[test',
Result: SELECT * FROM [test.dbo.t;drop table t; select * from
[test].[dbo].[t] WHERE [c] = @param_value

Example 2: select @DatabaseName = 'test].dbo.t;drop table t; select * from
[test',
SELECT * FROM [test]].dbo.t;drop table t; select * from [test].[dbo].[t]
WHERE [c] = @param_value

Thanks Alex,
Rubens

"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:7eaebcc1-2615-402a...@d1g2000hsg.googlegroups.com...