Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2014
    Posts
    18

    Unanswered: Can I store a list of values in a variable?

    Hello All! I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

    I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

    Any ideas/suggestions?

    Thanks so much!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you:- yes
    should you:- (in my opinion no)

    put the values into a table, one per row. pull the data as required from htat tabel, not a single value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2014
    Posts
    18
    I have read other threads warning against this, but when the user has a list of values that need manipulated I'm not sure how else to accomplish this and haven't seen any alternative suggestions....

    The list of values from the user could be quite long (100+ files), so to INSERT one at a time would be counterproductive. I was hoping to find a way to either insert them using some sort of loop or to otherwise use them as a variable.

    What is the "best practice" when a user has a list of values originating from some source other than the database and needs the database to address them?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you've not really described your requirement. so Im a little hesitant in 'best practice', and noting that I'm more a devleoper than a DBA.

    to me the 'right way' is an intersection table. have a table for files a table for people, and an intersection table that identifies files associated with users (and store anything else pertinent to that intersection. UI is through a list or combo box. however you'd have to populate the list/combo box yourself in the forms on current event, and save the combo box inthe forms after update event
    OR
    implement the intersection table as a sub form
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2014
    Posts
    18
    I'm not certain I can provide much more detail without confusing everyone, but I will give it a shot!

    Every week I get a list of files from a vendor reflecting payments made in their facility. In order to know how to post the payments on my end, I need to query that list (from the vendor) and get current balances (cost, principal, interest, etc). I'm trying to avoid looking up the cases in my system one at a time and manually calculating these figures in order to break out the payments.

    The biggest challenge in our system comes with the file numbers themselves. Vendor has only ONE of our file numbers per case, however we may have multiple files included in each case on our system. Hence, I need to take this list of files from the vendor, total the costs and principal for each CASE (could be 100+ numbers, might be just one!) in our system, and show the main file number (which may or may not be the one provided by the vendor), TOTAL costs, TOTAL principal, etc. for all accounts included in that case. It's mind-bending, I know!

    I've managed to get the query nearly there, but I want it to return and group by only the main file number such that the totals would show accurate balances by case (not by individual file).

    Because of the way our software is set up, we have certain challenges we have to overcome (such as identifying the MAIN file number). I figured if I had a list of the file numbers exactly as they came over from the vendor, then I could first find the correct main file numbers as well as group by that column in the results.

    Here is the code I'm using with "File1, File2, File3..." as a placeholder for the list of files:
    Code:
    SELECT master.Name, SUM(LCB.CC_Balance) as Costs, SUM(current1) as Principal
    FROM master
    LEFT OUTER JOIN Legal_Cost_Balance LCB ON LCB.Number = master.number
    WHERE master.number IN 
    (
    	SELECT linked_number
    	FROM Linking_LinkedAccounts LLA
    	INNER JOIN master ON master.number = LLA.linked_number
    	WHERE LLA.number IN 
    	(
    		File1, File2, File3....
    	)
    	AND master.assignedattorney IS NOT NULL
    )
    GROUP BY master.Name
    ORDER BY master.Name
    Hopefully this will help clarify.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Your vendor is sending this to you in a file correct? Why not read the file, rather than have someone input the data? You just read the file line by line. Batch processing has been around since before OLTP. Since you don't want to read it line by line, how about setting up an incoming request table and when you get that vendor file(which I am assuming is in a particular format), you then load it into the incoming request table.

    As an aside, stop using IN, use EXISTS. 99% of the time it will be faster. Also, by doing that and correlating this subselect you will not have to access the table twice.
    Dave

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Look up 1NF and why all values are scalar in RDBMS, NOT LISTS.

    The simplest answer is to use a long parameter list to construct lists and derived tables inside the procedure body. SQL server can handle up to 2100 parameters, which should be more than enough for practical purposes. SQL Server is actually a wimp in this regard; DB2 ;can pass 32K parameters. and Oracle can have 64K parameters

    Their database engines use those extreme limits for certain system functions that the users will never see. Nobody expects a human being to type in thousands of lines of CSV text with any of these techniques. In practice, I have seen a list of 64 (Chessboard), 81 (Sudoku solver) and 361 (Go board) simple parameters. Most of the time, 100 parameters is a very safe upper limit. If I need to pass more then I want to look at an ETL tool or something else.

    The main advantages of the long parameter lists are:

    1) The code is highly portable to any Standard SQL. One product bigots need to get over the idea that they are never going to see more than one SQL engine in their careers.

    2) The code is pure native SQL and not an external procedural language. The people that maintain it can be SQL programmers. There will be no need to send your C# programmer to F# classes next year to keep up.

    3) The optimizer will treat them like any other parameter. They can be sniffed. You can use a RECOMPILE option and get the best performance possible each time the procedure runs.

    4) The compiler will treat them like any other parameter. You get the expected error messages and conversion. Frankly, I have never seen anyone who used one of the other techniques write RAISERROR() calls to return the same error messages as the compiler.

    5) It is easy to generate the code with a text editor. Think about how hard it is to write in a CLR language you don't know or to write a loop in T-SQL. I just cut & paste a skeleton with 250 parameters then cut off what I need. I am going to show a list of five parameters in my skeleton code to save space.

    The simplest example of the long parameter list technique is just to use them: Pass what you need and leave the rest of he list to default to NULLs.

    CREATE TABLE Zoo
    (sku CHAR(11) NOT NULL PRIMARY KEY,
    animal_name VARCHAR(25) NOT NULL)

    INSERT INTO Zoo (sku, animal_name)
    VALUES
    ('39634-62349', 'Horse'),
    ('74088-65564', 'Cow'),
    ('16379-19713', 'Pig'),
    ('39153-69459', 'Yak'),
    ('17986-24537', 'Aardvark'),
    ('14595-35050', 'Moose'),
    ('40469-27478', 'Dog'),
    ('44526-67331', 'Cat'),
    ('93365-54526', 'Tiger'),
    ('22356-93208', 'Elephant');

    CREATE PROCEDURE Animal_Picker
    (@p1 CHAR(11) = NULL,
    @p2 CHAR(11) = NULL,
    @p3 CHAR(11) = NULL,
    @p4 CHAR(11) = NULL,
    @p5 CHAR(11) = NULL)

    SELECT sku, animal_name
    FROM Zoo
    WHERE sku IN (@p1, @p2, @p3, @p4, @p5);

    EXEC Animal_Picker '39153-69459', '17986-24537', '99999-99999';

    If you had a long list, there would be a lot of NULLs in the IN() predicate list. It would be a good idea to clean them out. But you cannot do that with a simple IN() predicate list. We need to get the data into a column in a derived table first. This is another simple text edit problem.

    CREATE PROCEDURE Animal_Picker
    (@p1 CHAR(11) = NULL,
    @p2 CHAR(11) = NULL,
    @p3 CHAR(11) = NULL,
    @p4 CHAR(11) = NULL,
    @p5 CHAR(11) = NULL)
    AS
    SELECT sku, animal_name
    FROM Zoo
    WHERE sku
    IN (SELECT in_sku
    FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(in_sku)
    WHERE in_sku IS NOT NULL);

    EXEC Animal_Picker '39153-69459', '17986-24537', '99999-99999';

    The use of the VALUES() constructor is new to SQL Server programmers, but other products have had it for awhile now.

    I have a two articles on this topic at Simple Talk;

    http://www.simple-talk.com/sql/learn...rameter-lists/

    http://www.simple-talk.com/sql/learn...sts---part-ii/

  8. #8
    Join Date
    Mar 2014
    Posts
    18
    Thanks for all your help, everyone. I've found a way to import the Excel file directly into a table in the database and I can play with the numbers that way. I think I'm on the right path now, and certainly with a better understanding.

    Many thanks!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •