storing variables in a text file??

  • I have a report that executes a stored procedure that in the simplest form is something like

    select calls from mytable where callername in ('Joe Jones','Bill Davis' ) etc etc

    The callername changes based on employees coming in and leaving the company. I had been updating the list in my stored

    procedure. The company would like to be able to add/remove people from this themselves.

    I was wondering if there was a way I could store the names in a text file and sql would look at that to see my callername

    I would make my query something like select calls from mytable where callername in (mytextfile.txt)

    maybe i have to declare a variable like declare @Callername=mytextfile.txt.

    Not sure need some direction thanks

  • Sounds like a good candidate for SSIS. You can empower HR/Managment to update the text file, etc. and use a package to pull in the contents of the text file into a recordset and perform transformations as needed from there. If you unfamilar with SSIS, a good place to start is http://www.sqlis.com and of course, BOL 🙂 Good Luck.

  • I'd just BULK INSERT into a table and go from there.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You can also use Excel and use OPENROWSET to read from it. I'd load it into a table so you don't have locking issues.

  • You could use the txt file with opendatasource/openrowset if you wanted, but it sounds like it would be easier to do it with a table inside of sql and then if non-sql-proficient end users needed to be able to change it you could create a front end or add it to the front end currently in use.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Any reason to not store the data in a table and give the appropriate people access to that table (via a simple front-end app)? That would be easier than making sure a text file stays in a format that can be read. Otherwise, what happens when someone starts adding notes to the text file, for example? Or moves the text file to a different folder? Or accidentally deletes the text file? Overwrites it with a recipe for plum pudding? Or if you end up with a couple of hundred people in the text file and it becomes difficult to manage?

    Also, I could be wrong, but it seems to me that you'll probably need some history in your data, which is much easier to maintain in a table than a file.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • you know i think I was over thinking this I probably will just go with a web front end and a table and allow the user to manage them that way. Not sure why I didn't think of that in the first place.

    Thanks guys

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

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