Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gibble
Starting Member
3 Posts |
 Posted - 2007-09-11 : 17:36:11
|
| So, here's the conundrum.We have ProcA that uses nText as one of it's parameters. It works fine.I'm writing ProcB, that calls ProcA and also accepts nText as a parameter.Unfortunately, I have to do a string replace in that nText field. But, I've got that sorted out, with a little UPDATETEXT voodoo.My input parameter, now exists unchanged as @Body and in #nText.txt with all the modifications completed.The problem, is I need to pass that #nText.txt value as a parameter to ProcA.I can't use 'SELECT @Body = txt FROM #nText' because I get an assignment error (can't assign nText values).And I can't seem to call ProcA with the value from the table directly. What can I do? I'm 99% there, I just need to take this last step and I can put this garbage behind me.Thanks-C. |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2007-09-11 : 17:56:17
|
| Do you really need ntext for the FROM clause. Can you do with varchar(8000) or less? |
 |
|
|
Gibble
Starting Member
3 Posts |
Posted - 2007-09-11 : 18:03:42
|
quote:
Originally posted by ValterBorges
Do you really need ntext for the FROM clause. Can you do with varchar(8000) or less?
Yes, it has to be nText...the data is ridiculous lengths.I just grabbed 20 records at random out of the table, nearly all the fields were 5 figures in length. Let's just take the half way point of 50,000. Being nvarchar(4000) is my limit, I'd need 12 fields to store this one...and I'd still have the problem of passing this to stored proc ProcA properly as nText.. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 18:14:25
|
| i guess you need to create a global temp table then so procB can access the value from procA?if that doesn't work, a permanent table?I'm not a guru but hopefully this helps you in any way--------------------keeping it simple... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-12 : 01:42:03
|
[code]SELECT @Body1 = substring(txt, 1, 8000), @Body2 = substring(txt, 8001, 8000), @Body3 = substring(txt, 16001, 8000)FROM #nText[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 06:38:36
|
| he'll need dynamic sql to pull that off--------------------keeping it simple... |
|
|
|
Gibble
Starting Member
3 Posts |
Posted - 2007-09-12 : 10:13:00
|
quote:
Originally posted by Peso
SELECT @Body1 = substring(txt, 1, 8000), @Body2 = substring(txt, 8001, 8000), @Body3 = substring(txt, 16001, 8000)FROM #nText
E 12°55'05.25"N 56°04'39.16"
Couple things wrong with that. First, I can't use 8000, I have to use 4000, it's nText, not a Text column. And not knowing how many fields I actually need...this isn't a very flexible solution.Second, while that gets me the data in variables, how do I then call the next stored procedure that has an nText argument?. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 14:22:21
|
quote:
Originally posted by Gibble
quote:
Originally posted by Peso
SELECT @Body1 = substring(txt, 1, 8000), @Body2 = substring(txt, 8001, 8000), @Body3 = substring(txt, 16001, 8000)FROM #nText
E 12°55'05.25"N 56°04'39.16"
Couple things wrong with that. First, I can't use 8000, I have to use 4000, it's nText, not a Text column. And not knowing how many fields I actually need...this isn't a very flexible solution.Second, while that gets me the data in variables, how do I then call the next stored procedure that has an nText argument?.
Put the updated data ina temp table, and then use that temp table in the stored procedure you are calling.CODO ERGO SUM |
|
|
|
|
|
|
|
|
The joys of nText