Dynamic SQL reading statements from table

[downwitch]

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
sp_executesql @sql

I get: Incorrect syntax near 'sp_executesql' .

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.

[manstein]

On May 1, 5:04 pm, downwitch <downwi...@gmai l.comwrote:

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
sp_executesql @sql

I get: Incorrect syntax near 'sp_executesql' .

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.

Try printing your @sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.

[manstein]

On May 1, 5:08 pm, manstein <jkelly.ad...@g mail.comwrote:

On May 1, 5:04 pm, downwitch <downwi...@gmai l.comwrote:

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
sp_executesql @sql

I get: Incorrect syntax near 'sp_executesql' .

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.

Try printing your @sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -

- Show quoted text -

as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.

[downwitch]

No, I have printed it, it's fine. No truncation. Like my example
above, I can't even get a simple short statement to work (my real
example is 42 characters), and I don't see the error.

On May 1, 5:13 pm, manstein <jkelly.ad...@g mail.comwrote:

On May 1, 5:08 pm, manstein <jkelly.ad...@g mail.comwrote:

On May 1, 5:04 pm, downwitch <downwi...@gmai l.comwrote:

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
sp_executesql @sql

I get: Incorrect syntax near 'sp_executesql' .

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.

Try printing your @sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -

- Show quoted text -

as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.

[Plamen Ratchev]

You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.

Just try:

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
EXEC sp_executesql @sql

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[downwitch]

OK, yes, that does solve the first problem, thank you. Now for
another, related. I'm using a variation on Erland's proc here
http://www.sommarskog.se/dynamic_sql.html#quotestring
to handle nested quotes. There are none, of course, in the simple
statement, but running it through the proc causes it to fail
nonetheless.

Here's my version of the function:
-----------
CREATE FUNCTION uQuoteString(@s tr nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @ret nvarchar(MAX),
@sq nvarchar(4)
SELECT @sq = ''''
SELECT @ret = replace(@str, @sq, @sq + @sq)
RETURN(@sq + @ret + @sq)
END
-----------

So running

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
SET @sql = dbo.uQuoteStrin g(@sql)
EXEC sp_executesql @sql

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

Note that the error has changed, no longer referencing the stored proc
but instead the @sql argument.
On May 1, 5:28 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.

Just try:

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
EXEC sp_executesql @sql

HTH,

Plamen Ratchevhttp://www.SQLStudio.c om

[Erland Sommarskog]

downwitch (do*******@gmai l.com) writes:

Here's my version of the function:
-----------
CREATE FUNCTION uQuoteString(@s tr nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @ret nvarchar(MAX),
@sq nvarchar(4)
SELECT @sq = ''''
SELECT @ret = replace(@str, @sq, @sq + @sq)
RETURN(@sq + @ret + @sq)
END
-----------

So running

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
SET @sql = dbo.uQuoteStrin g(@sql)
EXEC sp_executesql @sql

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

Note that the error has changed, no longer referencing the stored proc
but instead the @sql argument.

I added a PRINT @sql to your SQL batch, and this is what I saw:

'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.

A string on its own is not legal T-SQL.

I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.

Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

manstein (jk**********@g mail.com) writes:

as an addendum, what is your (MAX) size?

MAX implies in SQL 2005 a size of two gigabytes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Read that section in Erland's article again. The use of this function is
wrap an input parameter in quotes. I do not see any parameters in your SQL
statement, so no need to use the function.

Plamen Ratchev
http://www.SQLStudio.com

[manstein]

On May 1, 6:14 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

manstein(jkelly [e-mail] m) writes:

as an addendum, what is your (MAX) size?

MAX implies in SQL 2005 a size of two gigabytes.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.