Post reply

Dynamic T-Sql In Stored Procedure

  • February 12, 2003 at 10:18 am

    #80695

    I am using a stored procedure to parse and process an incoming XML file.

    I have a Setup table that contains amongst other things the With Clause to be used by OPENXML Statement.

    I am building my dynamic Sql Statement using the following Variables etc ....

    DECLARE @iDoc Int

    DECLARE @TagsToExtract Varchar(2000)

    DECLARE @sSqlSelect NVarchar(4000)

    DECLARE @OpenXmlXPath Varchar(100)

    Set @sSqlSelect = 'Select * From OPENXML (' + cast(@iDoc As Varchar(10)) +' ,''' + @OpenXmlXPath + '''' + ',2' + ') WITH(' + @TagsToExtract + ')'

    The problem I guess is the @iDoc Value which is passed from following statement which executes first:-

    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XmlString

    As the @iDoc value returned is an integer when the exec sp_executesql @sSqlSelect Statement runs the statement returns the following error:-

    Select * From OPENXML (211 ,'/root',2) WITH(ESTD-COST-MISC VARCHAR(1000),ESTD-COST-PAINT VARCHAR(1000),ESTD-COST-LABOUR VARCHAR(1000))

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near '211'.

    211 being the Value of @iDoc

    Any sugestions as to why ?

    Thanks in advance

    Andy

    Andy

  • February 12, 2003 at 11:34 am

    #448773

    I think you might need to escape the single quote. Try a replace('''', '''''') around the @idoc.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • February 13, 2003 at 9:56 am

    #448888

    Steve

    Thanks for your suggestion. Unfortunatly I still can't resolve the problem could you please give me a further example of the syntax I should use just in case I'm getting something else wrong.

    My Code is a follows:-

    Set @sSqlSelect = 'Select * From OPENXML (' + cast(@iDoc As Varchar(10)) +' ,''' + @OpenXmlXPath + '''' + ',2' + ') WITH(' + @TagsToExtract + ')'

    Thanks

    Andy

  • February 20, 2003 at 1:34 pm

    #449421

    Andy,

    Just a shot in the dark, but did you try using EXEC (@sSqlSelect) instead of using the sp_executesql procedure. It may be a problem when the string is internally converted to ntext (though I don't know why)...

    Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply