June 15, 2009 at 8:06 am
Hi there,
My Client application queries my database using ADODB.command objects. In the CommandText I put the stored procedure name. The parameters for the stored proc are passed as ADO.command parameters. For example: I have a stored procedure called "sp_Select_Company_Where_RegionID", then I send my ADO command along with RegionID as a parameter.
Now I have ended up with loads of really simple stored procedures.
It seems easier to me if the client builds an SQL string saying "SELECT dbo.Company.* FROM dbo.Company WHERE RegionID = " & MyRegionID . Then I'll just send that as the CommandText. Will this work just as well?
What's the usual practice?
Thanks
June 15, 2009 at 8:22 am
It will certainly work, but every time the SQL requires a change you'll have to update the code, recompile and re-distribute it.
If you have the stored procedures then you can change the SQL independent of the software that uses the database (.NET, VB or whatever).
Testing the alterations is easier too, as the changes are only on the server and so you can test within SQL Server and only involve the 'other' software at the end, to make sure the calls to it still work (and timeouts and such exciting things as that).
You can also work within SQL Server for performance issues that you simply couldn't do if the SQL is contructed elsewhere and executed via a connection.
And you can learn an awful lot about SQL Server by getting involved with SQL Server itself. It's another valuable skill that you'll miss out on - or you could end up employing SQL Server 'specialists' to solve performance or configuration issues as no-one had the chance to learn such things where you are.
Keep it simple. Divide the functionality where it is more suited. Let SQL Server handle the data as much as it can and pass it back to the software to show to the users and suchlike. The 'front end' can make sure the users can't enter stupid data and SQL Server can look after storing, retrieving and manipulating it.
I've worked in both types of places - those that construct the SQL commands within VB and fire it at the server and those that pass parameters to Stored Procedures. The latter has been easier to maintain (in my experience) and a lot more interesting too.
BrainDonor.
June 15, 2009 at 8:26 am
Jas_The_Ace (6/15/2009)
It seems easier to me if the client builds an SQL string saying "SELECT dbo.Company.* FROM dbo.Company WHERE RegionID = " & MyRegionID . Then I'll just send that as the CommandText. Will this work just as well?
It'll work, but it's a massive great big security hole. Google SQL injection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2009 at 8:30 am
Old question of faith...
I definitely prefer procedures/TVFs as database interface for many reasons. Today OR-Mappers handle more and more standard SQL, the good one allow to inject with own/custom SQL for INSERT/DELETE/UPDATE and complicated queries.
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply