| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2005-01-12 : 12:21:29
|
How do I pass text value to sp parameter that accepts text data type.I am reading a text field from a that contains comma delimited values and I want to pass this string to my function that delimits the value and returns a table.SET TEXTSIZE 65000Declare @LengthOfBlob as intDECLARE @ptrval varbinary(16)DECLARE @val varbinary(16)SELECT Top 1 @ptrval = TEXTPTR(MyBlog), @LengthOfBlob = DataLength(MyBlog) FROM BlogResultsREADTEXT BlogResults.MyBlog @ptrval 0 @LengthOfBlobSelect * From SplitString(@ptrval,',') |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-13 : 00:20:56
|
| I'm pretty sure there is a thread here, or possibly an article, which describes how to do a comma-delimited split on a TEXT column. That might answer the question (given that I don't know the answer!)Kristen |
 |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 12:49:44
|
| Yes I have written the function for splitting the text value to table... in this article:http://sqljunkies.com/WebLog/amachanic/articles/SplitString.aspxBut could'nt find a simple read from table... process data with sp by passing text value into sp...I had to write a vb app to test my code, but I would like to avoid this effort if I can do it in sql. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 21:11:29
|
quote:
Yes I have written the function for splitting the text value to table... in this article:http://sqljunkies.com/WebLog/amachanic/articles/SplitString.aspx
Hmmm....looks a lot like David's article (from last year):http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-13 : 22:48:32
|
| David's article shows a very clever technique -- but I believe mine is a bit faster :) |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-14 : 00:55:10
|
| Yes I did use his article to solve the problem of breaking up a text field into table format but my question is How do I read a text value from a table and pass the result into a stored procedure. |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-14 : 00:56:51
|
quote:
David's article shows a very clever technique -- but I believe mine is a bit faster :)
amachanic: Do you have a posted solution for this question? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-14 : 06:08:58
|
| >> How do I read a text value from a table and pass the result into a stored procedure.Don't believe you can in sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-17 : 14:20:49
|
| Probably not doable, agreed -- at least, not without using some very undesirable hacks.What's the stored proc doing w/ the text? Can you do it in a UDF? |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-17 : 14:23:06
|
| You could create a temp table in the calling sproc with a single row/column, of type text, insert the value into there, and then the child sproc could access it. That's still a hack, but not quite as bad as what I was thinking of (calling out to OSQL). |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-17 : 15:57:48
|
| Actually I like David's method because it handles:'a,b,c,d,,f'(which I get in my data when values are missing)Nice functions both though!rockmoose |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-18 : 18:38:04
|
| How was the efficiency ?rockmoose |
|
|
|
|
Passing text value to sp as Parameter