Posted by manstein on 05/01/07 21:08

On May 1, 5:04 pm, downwitch <downwi...@gmail.com> wrote:
> 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.

[Back to original message]