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

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.

• Next in forum: Re: Dynamic SQL reading statements from table
• Prev in forum: Re: Create One Trigger For Both Update and Delete
• Thread view: Dynamic SQL reading statements from table

[Reply to this message]