Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Dynamic SQL

Reply
Thread Tools

Dynamic SQL

 
 
Mark Goldin
Guest
Posts: n/a
 
      09-28-2009
I am trying to make something like this working:

ALTER PROCEDURE [dbo].[usp_createPrintJob]
@TableName char(20), @TableStruct nvarchar(MAX), @xmlstring nvarchar(MAX)
AS
BEGIN
DECLARE @SQLString NVARCHAR(500)

DECLARE @idoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring
SET @SQLString =
N'INSERT ' + @TableName +
' SELECT * FROM OpenXML(' + rtrim(ltrim(str(@idoc))) +
', ''VFPData/curdata'', 2) WITH ' + @TableStruct
EXECUTE sp_executesql @SQLString
END

when I run it I am getting an error:
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '42'.
Msg 319, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.

Please help..

Thanks

 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      09-29-2009
Mark Goldin wrote:
> I am trying to make something like this working:
>
> ALTER PROCEDURE [dbo].[usp_createPrintJob]
> @TableName char(20), @TableStruct nvarchar(MAX), @xmlstring
> nvarchar(MAX) AS
> BEGIN
> DECLARE @SQLString NVARCHAR(500)
>
> DECLARE @idoc int
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring
> SET @SQLString =
> N'INSERT ' + @TableName +
> ' SELECT * FROM OpenXML(' + rtrim(ltrim(str(@idoc))) +
> ', ''VFPData/curdata'', 2) WITH ' + @TableStruct
> EXECUTE sp_executesql @SQLString
> END
>
> when I run it I am getting an error:
> Msg 102, Level 15, State 1, Line 31
> Incorrect syntax near '42'.
> Msg 319, Level 15, State 1, Line 32
> Incorrect syntax near the keyword 'with'. If this statement is a
> common table expression or an xmlnamespaces clause, the previous
> statement must be terminated with a semicolon.
>

<grin>I feel like a broken record sometimes ...
<click>you cannot debug a syntax error in a sql statement without knowing
what that sql statement is.<click>you cannot debug a syntax error in a sql
statement without knowing what that sql statement is.<click>you cannot debug
a syntax error ...


You must print the contents of @SQLString if you want to stand any chance of
debugging it. Like this:

print @SQLString
--EXECUTE sp_executesql @SQLString

Run the procedure. Look at the printed sql statement. Compare it with what
you expected it to look like. Try to run it as-is. You should have spotted
the error by this point, whatever it is.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Build dynamic sql query for JSTL <sql:query> Anonymous Java 0 10-13-2005 10:01 PM
VPN between 2 Cisco routers (1 static, 1 dynamic) with access from stat --> dynamic over ISDN Hans-Peter Walter Cisco 3 01-21-2004 02:12 PM
Does Pix or cisco router support dynamic-to-dynamic IPSec VPN? c Cisco 2 01-13-2004 01:53 AM
Re: Dynamic Table with Dynamic LinkButtons Rick Glos ASP .Net 0 07-08-2003 01:09 PM



Advertisments