In a server -> client scenario, wouldn't it be simpler and faster to grant a public user access to a Stored Procedure rather than using web services (XML, REST, SOAP, etc) and other interoperable solutions?
That way you could connect directly to a database and get relevant content and do what you will with it in the same way across all languages and systems?
I think the key is right in your question:
In a server -> client scenario, wouldn't it be simpler and faster to grant a public user access to a Stored Procedure rather than using web services (XML, REST, SOAP, etc) and other interoperable solutions?
As any form of service provider, there are tradeoffs you make, one of these is often trading the flexibility of direct-access to something that power-user types appreciate, with the ease-of-use of a more interoperable solution.
So let's start off by saying there are security concerns. They may not be so much the SQL server itself, but rather the risks inherent everytime you expose a computer to the internet. The benefit of doing this with your web server instead of your database server is one of risk mitigation.
The train of thought generally goes like this: If your OS has a security flaw found, and your database server is exposed to the internet, someone may take advantage of that flaw to get admin on that exposed machine - voila All your data are belong to them. So what's usually done is web-servers that don't have any data and have hardly any permissions on the DB are exposed to the internet. If someone get's root on these, they should ideally only have permissions to do what the website can do so the abuser has really gained nothing.
Security concerns are (probably) the number 1 reason people don't do this in practice, but we'll assume an ideal world where an exposed database server doesn't have security concerns for a moment.
In this ideal world, what happens when you have some data you want exposed and you create and expose the stored procedure? Who have you just exposed this data to? The entire internet? Sort of. The reality is you just exposed this data to everyone with the skills, tools, and time to create a connection and access your stored procedure.
Now let's ask a slightly different version of the above question: what happens when you have some data you want exposed - who do you want it exposed to? If your main target is to expose that data to the internet at large, you'll want to create a web-page that displays that data. At this point, you've created the interoperable solution already, so now what value does the direct stored procedure access have? Same value as before I guess, except those power users would probably just use your interoperable interface instead of the SQL server one because it's easier.
Let's take another approach to the above thought - What if your main target you want to expose the data to is third party developers? Great, they probably have the skill, time, and tools to access your stored procedure directly! But wait a minute, if you have fixed set of consumers of your dataset, do you really need to expose the server to the entire internet? Not really, it's going to simplify things from a security and auditing perspective a lot if you create secure tunnels for your 3rd party consumers which puts them in an effective intranet with your DB - an intranet is exactly how people normally access DBs anyways so now you're back to the typical scenario.
So as I hope I've illustrated with some logical thought experiments and a bit of leading the witness, we can agree the times where such a scenario actually provides value are pretty slim. I suppose you could do it, and there may even be an edge case you hit where it actually makes sense. However as soon as you make that logical leap, you also have to recognize there are security risks. In conclusion: why bother?
The downside is that your client now knows that you're providing data from a database via stored procedures, and has to hook directly into that to get at the data. If you wanted to change to e.g. a NoSQL solution, or proxy the call on to somewhere else, or load-balance requests, etc etc etc, this is much harder if you don't have a layer in between the data and the client.
Essentially, if you have;
Client <====> Interoperability Layer <====> Data
Then the layer could be doing anything...or not very much. But you're now free to change what it does at any time, so long as the interface to the client doesn't change.
Apart from security reason. 1) Will every one knows how to access the data through stored procedure? 2) Will every once have querying tools in their PC\Laptop?
If it is a client requirement we need to satisfy it. Also, we need explain the security issues when database server is exposed to internet.
