January 7, 2003 at 9:02 am
I need to either append several varchar(8000) variables into a text variable or find a way to keep appending values to a text variable and then at the end be able to have the result in a text variable.
Here is my situation. I pass a text variable as an input parameter (long XML string) into Stored Procedure A. Stored Procedure A does manipulation on that input parameter and adds more to it and then passes a text variable as in input parameter to call Stored Procedure B. I have tried the following methods with no success. Can someone please help me with this?
* Tried to use a text variable type instead of varchar(8000) for the local variable. The problem here is that SQL doesn't allow you to create local variables of type text.
* Tried to determine any time the length of the local variable is reaching a length of 8000. At that point, create a new local variable. At the end, append all of the string variables into one text variable and then pass that to Stored Procedure B. The problem here is that I can't find a way to append several string variables into a local text variable.
* Tried to create an input variable of type text and then use it to assign the string to as we are building it up in Stored Procedure A. This works better because theoretically it can be assigned a long value and could be easily passed as a parameter to Stored Procedure B. The problem here is that you have to use temp tables to store the text values and the more complicated text updating functions in order to append text (temp tables to find the point in memory at the end of current text string to know where to add on the new text to append to the string). Then there is no way to get the value of the temp table back into a text variable to pass to Stored Procedure B. Stored Procedure B expects it as an input text variable - not reading it from a temp table.
Thanks in advance,
LaDonna
January 7, 2003 at 2:22 pm
I am afraid you have to accept the third way and live with it.
Another approach is to find a better design solution that does not require passing long XML texts to stored procedures.
January 7, 2003 at 2:27 pm
quote:
I am afraid you have to accept the third way and live with it.But the third way doesn't work to solve my problem either. I pointed out the reason why.
January 7, 2003 at 2:53 pm
Why not pull the process within SP B into SP A so that you don't have to deal with this. Not sure if it will work but it may be worth at least a try.
January 7, 2003 at 3:01 pm
Because I use Stored Procedure B from serveral different stored procedures - not just from Stored Procedure A. It is a very long complex stored procedure.
January 7, 2003 at 3:04 pm
Do you mind my asking what are you doing to the xml in stored proc A? and what is the purpose of stored proc B? I ask to see if we can provide you an alternative solution to what you seek to do.
Tim C.
//Will write code for food
Tim C //Will code for food
January 7, 2003 at 3:16 pm
these sprocs are used to support a set of web services that take a long xml string as input from the web service and then perform some logic and output the results.
some logic is performed on the original input string and then since another part of the logic is common across many of the sprocs, i placed it into sproc B to be called by the other sprocs.
(I am using OPENXML to parse the xml strings in both sprocs.)
January 7, 2003 at 3:22 pm
Why not perform this logic within the web service itself? Long string manipulation would be better suited for a language more suited to that task. (VB, C++, VB.Net, or C#)
IMHO out of all those VB 6.0 and below is the poorest at effecient string manipulation. But it is still better suited for it than T-SQL.
Tim C.
//Will write code for food
One Windows OS to rule them all, One Windows OS to find them,
One Windows OS to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 7, 2003 at 3:29 pm
I agree. Find a better design solution. Passing long strings to stored procedures and manipulating their is similar to coding business logic in stored procedures. It is generally a bad design approach.
January 7, 2003 at 3:30 pm
Because the code does so much database manipulation (inserts and updates) that it would have to call stored procedures hundreds of times if I parsed through in the Web Service.
January 7, 2003 at 3:40 pm
K, Are you modifying the xml directly through the text variable? Maybe instead using the sp_OA methods you could use the dom object directly in your code to work on the xml? Then you can pass the xml property into SProc B. After that, and your ealier suggestions I am tapped and would have to defer to someone of greater knowledge (THERE ARE MANY :D, My 3 year old son for one)
Tim C.
//Will write code for food
One Windows OS to rule them all, One Windows OS to find them,
One Windows OS to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 7, 2003 at 3:44 pm
I perform initial processing using OPENXML on the text variable. Then I need to modify format a bit to get it ready to be passed to sproc b. For that I was just using a variable of varchar(8000), but it isn't long enough. I am interested in learning more about what you mentioned in the last post, as I am unaware how to do that. Maybe your three year old could help?
January 7, 2003 at 3:45 pm
OK, so round tripping will be a problem.
Can the data be converted to a ADO recordset of some sort, after which it can loaded in a SQL temp table, processed, back to web, back to XML?
January 7, 2003 at 3:49 pm
I don't understand that last question. I suppose I could store all of the data in temp tables and then perform my processing, but I don't see how that is better than using OPENXML. It still doesn't help me get it into XML format to send to sproc b. I don't think I understand what you are trying to get at.
January 7, 2003 at 4:00 pm
LOL, if he could I would polish his resume... But as far the sp_OA methods are
concerned, it is nothing more than a way to create com objects in
TSQL (Like the MSXML2.DomDocument) Here are the pertinent methods to look up :
sp_OACreate, sp_OADestroy
sp_OAGetProperty, sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo, sp_OAStop
Instead of doing a poor job of explaining these myself, I have included a couple
of decent links to examples / explanations (Googled 'em):
Using Automation Stored Procedures to Access an Object's Properties
By Dianne Siebold
http://www.inquiry.com/techtips/thesqlpro/10min/10min0400.asp
How to Use sp_OAMethod
By Dianne Siebold
http://gethelp.devx.com/techtips/thesqlpro/10min/10min0500.asp
How to create a word document from a T-SQL SP
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=222&lngWId=5
Hopefully these give you an idea of how to read in the xml in SProc A,
manipulate it, then you could pass the xml into SPRoc B or you could even pass
the handle (reference) to the msxml2 domdoc into SProc B as an alternative.
Tim C.
//Will write code for food
One Windows OS to rule them all, One Windows OS to find them,
One Windows OS to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply