Post reply

How to accept/parse Text type in SP?

  • May 20, 2004 at 10:37 am

    #107879

    This may seems like a strange question, probably because it's not the best way to do it but I need some suggestions.

    The problem is such:  A user would like to send in a list of id's, comma delimited, to my stored procedure.  My first inclination was to use a varchar(8000) input parameter but soon learned this wouldn't work because the id length can grow over time.  So, my next idea was to use a Text datatype as the input parameter.  This is where I get over my head. 

    Is it possible to accept an input parameter of type "Text" and then break that string up into it's peices so I can store the individual values in a #temptable?

    Thanks for any ideas.

    Have a great day,

    Craig

  • May 20, 2004 at 12:51 pm

    #507176

    Do you have the ability to insert the text block into a table/column?

    Books online describes how to incrementally pull a large text block into a variable for working.  Do a keyword search on "READTEXT" in the Transact-SQL reference.

    Once you have the large string in a text column, you can use any of the "split" functions found here on the site to split the text at each comma.

    Your stored proc will need to make sure no characters are dropped if you parse through the text block in chunks.  There is no guarantee that you will split exactly on a comma, for example.

     

  • May 22, 2004 at 10:57 am

    #507382

    I agree that we can use split functions to get the data from the passed string.

    Just a better way of doing it would be to pass a XML string and then parse it using the system stored procedures. (Search for sp_xml_preparedocument in MS SQL Server books online)

    This way u have a clean interface and need not to worry abt the no of ids u pass.

    Naveen

  • May 22, 2004 at 1:04 pm

    #507384

    Hmm, that is an interesting idea, let me see if the caller would be willing to send me xml instead of ',' delimited string.

    Thanks everyone for your ideas, I really appreciate the help/ideas.

    Craig

  • May 22, 2004 at 3:59 pm

    #507385

    This article has a split function that takes an ntext parameter.  It also compares a few different ways of tackling the problem.

    http://www.sommarskog.se/arrays-in-sql.html

  • May 27, 2004 at 7:22 am

    #508002

    That is an excellent article, thanks Ted for posting it, it's exactly what I needed.

    As always, thanks for all the help with this.

    Have a great day,

    Craig

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply