Dynamic SQL

Discussion in 'ASP General' started by Mark Goldin, Sep 29, 2009.

  1. Mark Goldin

    Mark Goldin Guest

    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
     
    Mark Goldin, Sep 29, 2009
    #1
    1. Advertising

  2. Mark Goldin

    Bob Barrows Guest

    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"
     
    Bob Barrows, Sep 29, 2009
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rick Glos
    Replies:
    0
    Views:
    648
    Rick Glos
    Jul 8, 2003
  2. Chris Thunell
    Replies:
    3
    Views:
    3,430
    Edward
    Jul 21, 2004
  3. =?Utf-8?B?VGVyb3M=?=

    Dynamic Variables? OR Dynamic Controls

    =?Utf-8?B?VGVyb3M=?=, Aug 10, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    423
    Scott Allen
    Aug 10, 2004
  4. Anonymous
    Replies:
    0
    Views:
    1,480
    Anonymous
    Oct 13, 2005
  5. ecoolone
    Replies:
    0
    Views:
    767
    ecoolone
    Jan 3, 2008
Loading...

Share This Page