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. Advertisements

  2. Mark Goldin

    Bob Barrows Guest

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.