Dynamic SQL

[Mark Goldin]

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

 

[Bob Barrows]

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.