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 |
|
aaarrrgggghhh
Starting Member
6 Posts |
 Posted - 2004-08-18 : 05:38:04
|
| I have a stored procedure that as one of its arguments takes a text datatype. I need to call this stored procedure from inside a second stored procedure.The problem I have is that the value of the text argument in the first stored procedure need to be constructed in the calling stored procedure. The example below is not my actual stored procedure, but it illustrate the problem I have. CREATE PROCEDURE First @blah text AS --Do something GO CREATE PROCEDURE Calling AS DECLARE @thing text SELECT @thing = '' SELECT @thing = @thing + col_varchar FROM Some_Table --col_varchar is a column in table Some_Table of datatype varchar(10) EXEC First @thing GOThis however fails to work as you can not declare a local variable of type text. I can not use varchar(8000) as Some_Table will have many more entries than 8000. Is there some workaround whereby I can call 'First' from 'Calling' and have the argument '@thing' as the concatenation of a column of a table which will certainly exceed 8000 characters?Thanks in advance |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 09:06:10
|
| Can you elaborate on what it is you are concatenating to send to another proc and why? |
 |
|
|
aaarrrgggghhh
Starting Member
6 Posts |
Posted - 2004-08-20 : 04:59:09
|
| I'm concatenating a bunch of integers into one long string.The background to it is this:The project I'm working on has a bunch of Stored Procedures that as part of their input require a list of ID (in this case the ID of orders placed). As SQL does not support arrays, the strategy adopted for passing many IDs at once is this: Pass 2 variables IDs (text) IDLen (tinyint) where IDs is a string of all the IDs, each one padded to the same length as the longest with zeros. For example IDs = '001045234', IDLen = 3 equates to 1, 45, 234. This works fine when say a web page needs to call the SP as that can easily concatentate a long string and pass it to SQL. However, there are instances where I need SQL to call a SP that takes this form of input, therefore I need to be able to concatenate long strings in one SP and then pass them through to a second SP.The example I gave in my original post is kind of a striped down version of the problem, but its all there in essence.I know that the tactic for passing many IDs to a SP may not be the best, but due to the nature of the project I'm stuck with it and need to work uot how best to deal with it.Any ideas... |
|
|
|
|
|
|
Text datatype problem