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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-14 : 08:36:03
|
| Samp writes "Hi,I have a stored procedure which accepts varchar string which is of format "T10|T40|T90".This string is being parsed in the stored procedure and the individual strings are being entered as rows in a table.Now when the length of this "|" seperated string exceeds 8000 the sp is failing.So i changed the parameter to text data type but this is causing problems because text datatype cannot be treated as a varchar string i.e i cannot parse the text string and perform normal string operations on the text data type.What method is usually followed when a text data type string has to be parsed?Or What method is usually followed when a parameter to sp is a string whose length can be greater than 8000?Please help me out.Thanks and RegardsSampada" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-14 : 11:15:51
|
| What's the source of the data? 8000 is too smallThat's 2000 separate elements on each row....How many rows?Can you get at the data in a different manner?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-14 : 12:19:02
|
| You could use multiple variables in your stored procedure. Each variable would hold a chunk of the string.CREATE PROC usp_SomeProc(@Var1 VARCHAR(500), @Var2 VARCHAR(500), @Var3 VARCHAR(500)...)AS...Then your stored procedure would handle these multiple variables and insert them into the table as rows.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-14 : 22:25:52
|
| It sounds like you would be best off doing your string parsing at another tier. Some ideas :1. Break the string up into lots of 8000 characters and pass it in chunk by chunk.2. Break up the string into individual records and call the proc many times.3. Break up the string and write it into a file that you can BCP or DTS into your database.Unfortunately, T-SQL is pretty limited when it comes to working with Text data, so are in a world of pain if you try to kludge it.Damian |
|
|
|
|
|
|
Parameters in Stored Procedure