Parsing Parameters in a Stored Procedure

  • The definitive answer for passing a list as a parameter is:

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

  • unfortunately, I have both xml and looping to handle this situation. I tend to lean towards the xml because of the flexibility and descriptiveness. The flexibility is that we can pass several different parameters in as well as several values of the same parameter. At the same time, we(my shop) has a looping function that returns a result set for the SRS situation. That function is used like this:

    SELECT *

    FROM xyz

    WHERE (@par IS NULL OR xyz_id IN (SELECT entry FROM udf_split(@par, ',')))

    AND (@par2 IS NULL OR abc_id IN (SELECT entry FROM udf_split(@par2, ',')))

    I was quite frustrated once I saw the looping because I had pointed the developer at Jeff's tally table article but by that time it was to late. Already in production.

    Below is the xml snippet we continue to copy and modify for each situation.

    CREATE PROCEDURE [dbo].[usp_partBomADD]

    @part_id INT

    , @xmlPartBom VARCHAR(1000)

    AS

    BEGIN

    -- our xml document

    DECLARE @doc VARCHAR(1000);

    SET @doc = '<root>

    <parameter part_id="1" description="my part" usage="1"/>

    <parameter part_id="2" description="my part" usage="1"/>

    </root>';

    -- xml table handle

    DECLARE @hdoc INT;

    --declare table variable to hold our data

    DECLARE @tblXML TABLE (

    id INT IDENTITY(1,1)

    , part_id INT

    , description VARCHAR(50)

    , usage INT

    )

    --Create an internal representation of the XML document.

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

    -- query the xml for data

    INSERT @tblXML

    SELECT part_id, description, usage

    FROM OPENXML (@hdoc, 'root/parameter')

    WITH (part_id INT, description VARCHAR(50), usage INT);

    -- Remove the internal representation.

    EXEC sp_xml_removedocument @hdoc

    --SELECT * FROM @tblXML

    END

  • Personally, I would lean towards a tally table approach, but I can see where the simplicity of this approach would be favorable to some of my non-sql-I-don't-want-to-touch-it-unless-I-have-to developers.

    What do you guys think of checking for invalid entries with this?

    If @@RowCount <> (LEN(REPLACE('as,df,sa,as,',',',''))/2)

    Begin

    ...

    End

  • Jeff Moden (8/10/2010)


    TheSQLGuru (8/10/2010)


    Didn't we have a MASSIVE thread here on SSC.com on string parsing, with amazing code samples and wonderful benchmarks??

    We did. I tried to voice an objection about using all the same data for all the rows in any given test table (delimiters ALL in the same position causes lightning speed due to the way things are cached) but no one listened. All is not as it seems on that thread because of the test data that was used. As a result, I'd recommend retesting any code found in that thread before you use it.

    Where was that thread?

    I have seen a few here and there (one was a QOTD).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for the article.

    I think one must be careful about the string parsing method used. I don't believe there is a one size fits all method for all scenarios. I have used a different version here and there depending on the needs (one was CTE based, and another uses the XML)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice article. Nice code.

    Thanks,

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • In sql2005 in my application we have lists of primary keys passed to functions so we can't use a lookup table approach as in the article. I've seen parse routines before but didn't like their chopping method so I rewrote it to handle any delimiter and null values and using pointers.

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.StringParse') AND xtype IN (N'FN', N'IF', N'TF'))

    DROP FUNCTION dbo.StringParse

    GO

    CREATE FUNCTION dbo.StringParse(

    @STR varchar(max),

    @delimiter varchar(20),

    @emptytonull bit)

    returns @tokens TABLE (rownbr int PRIMARY KEY NOT NULL, token varchar(max) NULL)

    AS

    -- written BY Bill Talada

    BEGIN

    /*

    split a string INTO tokens AND RETURN them IN a TABLE.

    empty string returns one NULL row

    a string WITH only a delimiter returns two NULL rows, one before it AND one after it.

    a delimiter at the beginning means a NULL value appears before it.

    two delimiters IN a row implies a NULL value BETWEEN them.

    a delimiter at the END OF string implies a NULL value after it.

    */

    DECLARE

    @delimiterlength int,

    @currptr int,

    @nextptr int,

    @token varchar(max),

    @rownbr int

    SET @delimiterlength = datalength(@delimiter)

    SET @rownbr = 0

    SET @currptr = 1 - @delimiterlength

    SET @nextptr = charindex(@delimiter, @STR, @currptr+@delimiterlength)

    --SELECT @currptr, @nextptr, @delimiterlength

    WHILE @nextptr > 0

    BEGIN

    SET @rownbr = @rownbr + 1

    SET @token = substring(@str, @currptr+@delimiterlength, @nextptr - (@currptr+@delimiterlength))

    IF len(@token) = 0

    BEGIN

    IF @emptytonull=1

    SET @token = NULL

    ELSE

    SET @token = ''

    END

    INSERT INTO @tokens VALUES (@rownbr, @token)

    SET @currptr=@nextptr

    SET @nextptr=charindex(@delimiter, @STR, @currptr+@delimiterlength)

    END

    -- last row

    SET @rownbr = @rownbr + 1

    SET @token = substring(@str, @currptr + @delimiterlength, datalength(@str) - @currptr + @delimiterlength)

    IF len(@token) = 0

    BEGIN

    IF @emptytonull=1

    SET @token = NULL

    ELSE

    SET @token = ''

    END

    INSERT INTO @tokens VALUES (@rownbr, @token)

    RETURN

    END

    go

  • Short of SS 2008 my favorite is a table valued function... I've used this quite a bit and it never fails me, except for the < character which is invalid in XML. A couple lines of code within the function cleans that up nicely as well.

    Create FUNCTION [dbo].[UDF_TBL_ConvertStringToRows](@Data Varchar(Max), @Delimiter Varchar(10))

    Returns @DataTable Table (

    Id Int Identity(1,1),

    Data Varchar(Max))

    As

    Begin

    Declare @TextXML XML;

    Select @TextXML = Cast('<Root><Row>' + Replace(@Data, @Delimiter, '</Row><Row>') + '</Row></Root>' As XML);

    Insert @DataTable(Data)

    Select X.a.value('.','Varchar(Max)') As Data

    From @TextXML.nodes('/Root/Row') X(a)

    Return

    End

    --Test the Function

    Declare @Parm varchar(max)

    Select@Parm = '1,2,3,45,1001,2001'

    SelectId, Data from dbo.UDF_TBL_ConvertStringToRows(@Parm,',')

    Select@Parm = '"Data test" | Code Test'

    SelectId, Data from dbo.UDF_TBL_ConvertStringToRows(@Parm,'|')

    HTH

    RJ

  • I know XML in SQL Server 2008 has been greatly enhanced, but the question is me and a lot of people I know are still working on SQL Server 2000. I work in a big enterprise that don't change quickly, and our developments for the last eight? ten? years have been on this old but still valid version (this year I know they are deploying some SQL 2005, so maybe soon we'll have to change our mind...)

  • steveplate (8/10/2010)


    I wonder how this performs if the table you are searching is 20K+ rows. It seems that this might be faster only for really small tables such as states. Has anyone done any benchmarks?

    Hi steveplate!

    Thanks for your question.

    Sure it won't be very quick as we have no index gain using a function. The performance, being a question sometimes, it's not a point here: we needed to serve a multivalued API to our customers to avoid them querying their SP for every needed value. The chopping string loop was also valid but, in my opinion, the table method is more elegant and simpler.

  • While I don't have a SS2000 test bed, I'm fairly certain XMl was in 2000. The code I posted works on 2005 for certain.

  • jcboyer-1091017 (8/10/2010)


    I have been using an XML string to achieve the same results. Not sure that it is faster, but it is quite flexible as:

    EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLParameters

    insert into #T

    select *

    FROM OPENXML (@idoc, '/OrderGroup/Item', 1)

    WITH #T

    EXEC sp_xml_removedocument @idoc

    In this example #T is a large table with over 20 columns. @XMLParameters is varchar(max) to pass as many rows as necessary at once, which is and added bonus, because there is still no Table as parameter. (I am using varchar instead of XML because XML is more strict and we do not need this here.)

    Comments on this approach are welcome.

    Jerome

    Hello Jerome!

    As I have stated in others posts, XML was not an option for me (SQL2K)

  • RJ Roberts (8/10/2010)


    While I don't have a SS2000 test bed, I'm fairly certain XMl was in 2000. The code I posted works on 2005 for certain.

    Yes, but as far as I remember it was only the extension of SELECT ... FOR XML

  • john.moreno (8/10/2010)


    The definitive answer for passing a list as a parameter is:

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

    I'm a devote from Erland's work, and it makes me happy to admit I personally had his help once I needed it (a lot).

    That said, if you go to the link you mention, you'll see that does not refer to a method such as that presented here.

    Maybe it is by it's lack of generality (it doesn't work for free text parameters, only those in the reference table)... or simply he didn't thought of... 🙂

  • janusz pazgier (8/10/2010)


    Nice article to provide help for parsing delimited values, but I like better XML version provided by some user in the comments which is simpler but maybe more resource consuming.

    But my question is why this article has title :parsing stored procedure parameters if you just looking for specific switch value in single parameter? You are not looking for pair Name=Value, just for values. Could you provide example how you use values received from your parsing process? Because maybe all your work is not really required if you next do something based on your parsed values using for example CASE statement with fixed values and you have to do word search again later in he code.

    Could you provide example of usage of parameter values received from your parse routine?

    Hi janusz!

    The key of the usage: let's say you have a string as 'WA,CA,TX' (they would be spanish provinces, in my case 🙂 ), and with this method you get a temp table with the 3 values one-for-row.

    With this table, you can filter the main data table using an INNER JOIN:

    SELECT <some_data>

    FROM main_data_table AS a

    INNER JOIN #parameter_constructed_table AS b

    ON a.state = b.state

    You could also get the string directly in the WHERE clause but it only works with dynamic SQL (and you know it's dangerous rely on it, as it's prone to syntactic errors and possible SQL injections):

    Declare @param varchar(20)

    Set @param = 'WA,CA,TX'

    Declare @sql varchar(8000)

    Set @sql = '

    SELECT <some data>

    FROM main_data_table

    WHERE state IN ( ''' + @param + ''' ) '

    EXEC (@sql)

Viewing 15 posts - 31 through 45 (of 124 total)

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