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 |
|
shanly
Starting Member
17 Posts |
 Posted - 2005-12-28 : 10:07:16
|
| Hi,I wrote a stored procedure to concatinate multiple records in one field of data type - text. the procedure i wrote is shown below, one input paramater and one output parameter. the output parameter is text.create proc test (@id int,@@res text output)asset @@res = ''select @@res = @@res + Ltrim(rtrim(convert(char(20),box_number))) from customer where customer_id = @idreturnbut i receive the errorServer: Msg 409, Level 16, State 1, Procedure test, Line 7The assignment operator operation cannot take a text data type as an argument.Server: Msg 403, Level 16, State 1, Procedure test, Line 8Invalid operator for data type. Operator equals add, type equals text.Please help me in this to solve my problem.... |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-28 : 10:36:58
|
| text datatypes are a real pain in the a$$ to work with in t-sql code. Do you have the option of changing your datatype to varchar or nvarchar?Be One with the OptimizerTG |
 |
|
|
shanly
Starting Member
17 Posts |
Posted - 2005-12-28 : 10:51:21
|
| I need to use text datatype as the output data is too big. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-28 : 11:09:02
|
| Are the values you are concatenating also text? like box_number from your example above? In any case, it might be easier to use your application to construct the string from multiple database calls.The bottom line is you can't assign a value to a text type variable. The only way to get a @text variable assigned is as an input parameter to an SP that is called from an application.You could use a table with a text column and use updatetext to build to add to it but that's pretty ugly.Be One with the OptimizerTG |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-29 : 02:09:35
|
| >>I wrote a stored procedure to concatinate multiple records in one field of data type - textWhere do you want to show the concatenated data?If it is only for display, why cant you do this in your presentation layer if any?MadhivananFailing to plan is Planning to fail |
|
|
|
shanly
Starting Member
17 Posts |
Posted - 2005-12-29 : 04:49:24
|
| I want to concatinate multiple records of datatype varchar to a text datatype and view it in output parameter of a stored procedure. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-29 : 06:18:18
|
| Well. Do you want to show this in your Front End Application?MadhivananFailing to plan is Planning to fail |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-29 : 08:08:18
|
quote:
quote from cross postyea it is same, but i did not get a correct answer...
You mean, you did not get the answer you were hoping for. The answer you received is correct. As I (and sql server) have told you, it's impossible to assign a value to a text type variable with t-sql code. Madhivanan was offering an alternative of using whatever was consuming your output variable could also do the concatenation. Now if that is another t-sql SP rather than an application then I'm afraid you're out of luck. You need a different strategy.From books online (about use of SET for a variable):quote:
@local_variableIs the name of a variable of any type except cursor, text, ntext, or image.
Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
stored procedure