Concatenate varchar fields in call to stored procedure??

Question

Post reply

Concatenate varchar fields in call to stored procedure??

Ryan Torborg

SSC Enthusiast

Points: 142
More actions
July 2, 2007 at 1:37 pm

#117242

Hello all,
I have a stored procedure with an NTEXT input. I'm trying to come up with a way to pull the data from my table (ntext field) and pass it into this SP. I've been trying various methods, but nothing seems to work....is this possible?
EXEC mySP 1, 1, 1, field1+field2+field3
where field1, field2, and field3 are varchar(4000) fields.
Any help would be greatly appreciated. Any other ideas for getting this information into my SP?

Viewing 15 posts - 1 through 14 (of 14 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

You just discovered the problem with "text" fields

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I believe table does not disappear just because you call SP.

Why you cannot pass just reference as a parameter and read the text from the same table inside SP?

_____________
Code for TallyGenerator

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 3

The value is ultimately being used in an OPENXML function, so I need a way to pass it into SP_XML_PREPAREDOCUMENT.

I haven't figured out how to do that yet.....do you have any tips?

Sergiy SSC Guru Points: 110208 More actions · Answer 4

How come XML appeared in a table column?

You suppose to catch XML on its way in and process it immediately.

Look at SP which saves XML into the table and use SP_XML_PREPAREDOCUMENT there.

_____________
Code for TallyGenerator

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 5

Unfortunately, that's not an option. I can't modify the data model/application.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Unfortunately, that's the only option.

What's the data model/application you can't modify?

Does it drop XML into table directly?

Does it use any SP for it?

_____________
Code for TallyGenerator

BINU PRASAD.M SSC Enthusiast Points: 120 More actions · Answer 7

There is no way to pass char more than 8000 to stored proc. Only way is to read the text as 8000 slices and parse it using sp_preparedocument. Be aware that put your own start tag and end tag to make the xml part as full formed.

You will not be able to assign text to a variable,

You will not be able to call stored procedure from function.

Only thing possible is to pass as parameter but i was not able to find any use of that.

Need to write complete logic to spit and parse the xml.

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 8

It's a third-party app. It's kind of a black box, so I don't know their process. Regardless, it's sounding like this is a much bigger challenge than I expected.

Thanks for everyone's input.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

OK, black box,

but how XML ends up in the table?

You may use Profiler to catch the process storing XML.

If it uses some SP you may call another SP (created by you) from there and process that XML immediately.

_____________
Code for TallyGenerator

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 10

I'll have to look into it to see if I can determine how it is getting into the table. It's being stored real-time through a web app, so I don't think I'll be given permission to touch the process. I'll definitely look into it, though.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

This may also be a good time to explore upgrading to SQL Server 2005 where you can actually store XML data using the new XML data type.

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 12

That's in the works for the future, but not soon enough...

Michael T SSC-Addicted Points: 479 More actions · Answer 13

I'll go out on a limb: I haven't tested this, but what about cheating by putting the data from the 3 fields into a ##temp_table and using the ##temp_table in the stored procedure?

~Michael T

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 14

Do you know how I would go about getting the data from the temp table to use as an input parameter for sp_xml_preparedocument.