|
Jörgen Andersson wrote: 2) Then you need to use dynamic SQL, which is a whole can of worms by itself, and also a possible source of so called SQL-injection
I'm just talking about a local resource that I would use to call up previous queries that I've had to do to examine or update data manually. All of our work is done view SPs -- there are no ad-hoc SQL that are allowed to run against our DBs.
|
|
|
|
|
We have a database for saving queries like that as stored procs. Everybody has their own schema, and can see each other's procs. Most of them are specific to the table/view being queried.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
|
That's a good one. I'm checking it out. After looking at a couple of those templates I see where they got some of the boilerplate that they use in our RoundhousE[^] scripts.
Thanks, I will look more closely at this one.
|
|
|
|
|
My current project has a folder in google drive (coworkers choice, I'd've added a folder in our git repo) to store all the supposedly 1 off queries (if they said they wanted it repeatedly we'd have a report/import page) we've ran for the customer so they're available for the next time they ask.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
|
Yeah, I did something similar by creating a document on Google Drive. Just wish there was a better way to manage them with comments etc. By "better way", I mean i could just think of a query I've done in the past and it would appear on my screen. 
|
|
|
|
|
The solution to your problem with storage, retrieval and categorizations is obvious:
Put them into a database.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
W∴ Balboos, GHB wrote: Put them into a database.
well i have them in excel so ive already done that.😄
|
|
|
|
|
|
"This is the way."
~Mandalorian
probably best way for me too
|
|
|
|
|
MSSQL snippets works perfectly for me except you need to rabbit through the category folders to find the exact one you need  . Tis a bitch when you can't remember the category/folder it lives in.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Quote: There is no good way to store good SQL queries for future use.
What's wrong with stored procedures?
|
|
|
|
|
Member 13301679 wrote: What's wrong with stored procedures?
I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries.
We use SPs for all interactions with DB but I'm talking more about a code reuse type of thing.
I guess I could create a library of SPs and then run those when I need to, but I'd still need a way to manage them with names and stuff so I could remember what they do.
I guess it is really because I'm in the midst of designing queries for a large system and I only have to do this every 2 or 3 years so I have a long time away from it.
|
|
|
|
|
Quote: I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries.
MySQL Workbench allows that, including loading and saving the files just like an IDE, keeping files in different tabs, etc.
|
|
|
|
|
Looks interesting. I'm checking that one out. Looks similar to SSMS (Sql Server Mgmnt Studio)
|
|
|
|
|
|
That does look good but...
Link provided said Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
|
|
|
|
|
|
|
A lot of tools come to mind on this topic.
Notepad, or Notepad++:
I recommend using "AGENT RANSACK" a very fast multi-threaded Search engine.
(You can start the search in your ROOT file for the SQL files)
(Side note: This is my number one favorite tool)
Organize the SQL files, by INDEXING Them (aka: "Tags")
INDEX: (Example)
#DATE Ex: 01/01/2021
#PROJECT Ex: (NAME)
#TYPE Ex: (SELECT INSERT UPDATE DELETE)
#NOTATION Ex: That query for the thing I had to do at 1AM
...more as needed
----------------------------------------------------------------
<SQL HERE>
Open "Agent ransack" and search on terms in the sql.
This could be all put in a database, but I think that may be a bit too much.
You want something simple, and you want to find it fast.
You already have a body of SQL that is not indexed.
Start indexing all NEW SQL FILES, and as you revisit the old ones, (that are not indexed),
Index them as you go along.
Keep it all in one file, or multiple files, doesnt matter much with Agent Ransack.
So what you wind up with is your very own Google Search for your sql.
Keep It Simple, keep it moving.
|
|
|
|
|
Interesting. I shall consider it.
|
|
|
|
|
A side benefit - I think you will really like Agent Ransack - as a bonus.
I have used this tool for years. Its the one tool I cannot live without.
You know you got the queries.. you just need help finding them.
Comment them over time. Comment new ones when you make them.
Then let Agent ransack do the work.
Keep It Simple, keep it moving.
|
|
|
|
|
I would suggest a couple of things:
- a stored procedure that is just a collection of these wonderful SQL statements that is commented well and set up to NOT be runnable (comment out the whole thing between /* and */, probably)
- set up a comment area above each piece of code with keywords or details about what the code does and what it would be useful for
- using a search (CTRL-F) on the stored procedure you called GreatCodeToMaybeReuseSomeday (or whatever) to find the SQL of interest.
- Copy and paste the code you found.
Probably the closest to "think it and it will appear"
|
|
|
|
|
Just a thought: Use a library like JsonQL to convert SQL to a JSON representation and store them in MongoDB with descriptive metadata. Search on the content and/or metadata.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
