none
What is the best way to select any number of filters for a specific Id on a query in sql server environment.

    Вопрос

  • I'm trying to select any number of orders and call a stored procedure only one time.  So my question is in order to peform a select statement in  a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds?  Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way?  I'm looking for a better way to perform this operation that is reliable.

    example:

    select * from tblOrders where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

    Marc Noon
    21 апреля 2011 г. 1:14

Ответы

  • Dear Marc,

    You can replace this "OR" condition with "IN" statement. But you need to use one additional function to get the values.

    Like:

    select * from tblOrders where OrderId IN (1, 2, 1000)

    To get 1, 2 and etc..you need to pass a string to a function and get the table to pass to IN statement like below

    select * from tblOrders where OrderId IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)

    Find Script for this:

    ALTER FUNCTION [dbo].[UDF_Get_ArrayTable]
    (
        @ArrayString    VARCHAR(MAX)
    )

    --Return Table   
    RETURNS @ArrayTable Table
    (
        ArrayData INT
    )

    AS

    BEGIN

        --Declare Function variable
        DECLARE @Delimiter    VARCHAR(10)
        SET @Delimiter = ','
       
        --Get the string
        DECLARE @data NVARCHAR(MAX)
        SET @data = @ArrayString
       
        --using xml
        DECLARE @textXML XML;
        SELECT @textXML = CAST('<d>' + REPLACE(@data, @Delimiter, '</d><d>') + '</d>' AS XML);

        --Return Array Table
        INSERT INTO @ArrayTable(ArrayData)
        SELECT LTRIM(RTRIM(T.split.value('.', 'NVARCHAR(MAX)'))) AS ArrayData
        FROM @textXML.nodes('/d') T (split)
       
        --Return Function
        RETURN
    END

    /*------------- UNIT TEST CASE -------------------------

    CREATE TABLE #ABC
    (
        ID INT
    )
    INSERT INTO #ABC
    SELECT 1
    UNION
    SELECT 2

    DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5)
    SELECT @data = '1,2,3,4,5'

    SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data) -- return from Function.

    SELECT * FROM #ABC
    WHERE ID IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)) -- Check with Data
    --------------------------------------------------------*/

    Please verify this and try. Hope helps you

    Thank you.


    - Mishra, Sandeep(Success is a journey, not a destination.:)
    • Помечено в качестве ответа mnoon 13 октября 2011 г. 0:07
    21 апреля 2011 г. 7:18

Все ответы

  • Dear Marc,

    You can replace this "OR" condition with "IN" statement. But you need to use one additional function to get the values.

    Like:

    select * from tblOrders where OrderId IN (1, 2, 1000)

    To get 1, 2 and etc..you need to pass a string to a function and get the table to pass to IN statement like below

    select * from tblOrders where OrderId IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)

    Find Script for this:

    ALTER FUNCTION [dbo].[UDF_Get_ArrayTable]
    (
        @ArrayString    VARCHAR(MAX)
    )

    --Return Table   
    RETURNS @ArrayTable Table
    (
        ArrayData INT
    )

    AS

    BEGIN

        --Declare Function variable
        DECLARE @Delimiter    VARCHAR(10)
        SET @Delimiter = ','
       
        --Get the string
        DECLARE @data NVARCHAR(MAX)
        SET @data = @ArrayString
       
        --using xml
        DECLARE @textXML XML;
        SELECT @textXML = CAST('<d>' + REPLACE(@data, @Delimiter, '</d><d>') + '</d>' AS XML);

        --Return Array Table
        INSERT INTO @ArrayTable(ArrayData)
        SELECT LTRIM(RTRIM(T.split.value('.', 'NVARCHAR(MAX)'))) AS ArrayData
        FROM @textXML.nodes('/d') T (split)
       
        --Return Function
        RETURN
    END

    /*------------- UNIT TEST CASE -------------------------

    CREATE TABLE #ABC
    (
        ID INT
    )
    INSERT INTO #ABC
    SELECT 1
    UNION
    SELECT 2

    DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5)
    SELECT @data = '1,2,3,4,5'

    SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data) -- return from Function.

    SELECT * FROM #ABC
    WHERE ID IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)) -- Check with Data
    --------------------------------------------------------*/

    Please verify this and try. Hope helps you

    Thank you.


    - Mishra, Sandeep(Success is a journey, not a destination.:)
    • Помечено в качестве ответа mnoon 13 октября 2011 г. 0:07
    21 апреля 2011 г. 7:18
  • I basically did the same thing, just pulled everything into a single varchar(max) comma delimited, and parsed each id and placed that id into a temp  table which has only one column ie an id column sort of like what you did, but i used an inner join with my temp table. I did have to cast to int in order for the inner join to be very fast and zippy as this is geared for production.


    Marc Noon
    29 апреля 2011 г. 6:41
  • Hi Marc, Sounds good to me. Inner join is better way.

    Hope your problem solved as you are following correct way. Please mark as answer if my answer help you or same as what you are following.

    Thank you!


    - Mishra, Sandeep. (Success is a journey, not a destination.:)
    29 апреля 2011 г. 9:20