Posted by downwitch on 05/01/07 21:45

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(@str 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.uQuoteString(@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...@SQLStudio.com> wrote:
> 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.com

[Back to original message]