storing variables in a text file??

Question

storing variables in a text file??

Gail-378561

SSCommitted

Points: 1602
More actions
December 11, 2007 at 7:46 am

#177496

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

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

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

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.

Tommy

Follow @sqlscribe

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 4

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/

GSquared SSC Guru Points: 260824 More actions · Answer 5

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

Gail-378561 SSCommitted Points: 1602 More actions · Answer 6

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