February 11, 2011 at 8:27 am
Apologies if this has been posted many times, it’s the sort of question I would suspect has, but I’m not really sure what to search for, so have not found what I want
I have a commercial database with an SQL backend, for which I have written a number of views that I have brought into excel via the data connection, and they work fine. But they are fixed queries that the end user simply refreshes to get an update
What I want do is have a simple query such as below where the end user can enter the required serial number and it returns the company name associated (many of our customers have 100’s of serial no’s but this task is just to quickly identify who has a particular one
---select a1.company, a2.serialno from
Compdetails as a1 Join Products as a2
on a1.id = a2.id
where a2.serialno = 'xxxx xxxx xxxx'
---
I would like to be able to do this in excel, my ideal would be that the serial number is entered in one cell and the Company name appears in an adjacent cell
Is that possible, or is there another simple way of doing this, html possibly?
Cheers
February 11, 2011 at 8:38 am
Wayne
Create a stored procedure in your database that has parameter SerialNo, then write a macro in your Excel workbook that passes the parameter from the worksheet to the stored procedure and returns the result to the adjacent cell.
John
February 11, 2011 at 9:05 am
Thanks John
I know this might sound a bit daft as I have been using this database for years and have happlily written loads of views
but back when I started i was given two pieces of info,
1. Start your queries with "select" and you'll do no harm
2. Use views they're great and they're safe
And i have to confess, I have.
But to be fair, (and i hope this don't hex me) this info has served me well as I have never suffered any problems and have always got the info i want.
So what's the crack with stored procedures, (this database came with loads) anything i need to be aware of, can you recommend any good sources of info?
Cheers
Wayne
February 11, 2011 at 9:12 am
Wayne
There's been a lot of partisan debate about whether and how to use stored procedures, so I'll let you search for that and make your own mind up!
From my own point of view, stored procedures are good for the following reasons:
(1) They keep the data processing in the data layer, where it belongs
(2) You don't need to grant access to the underlying tables so it's more secure
(3) The parameterisation provided by stored procedures can help to prevent SQL Injection
(4) If you allow users or developers to put ad hoc code in spreadsheets and elsewhere, you can end up with performance problems
John
February 11, 2011 at 9:26 am
I'll be honest bud
I have cast an envious eye in that direction for a while, it's problaby time to take a look,
I'll spend an evening or two looking into it and have a go
Anyway, thanks for giving me a prompt on this
Wayne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply