Is it possible to call a stored procedure within javascript on the client side?
I know how to do on the server side, but I am interesting in doing on the client side.
Basically it boils down to directly contacting a SQL server from within the client. Is that possible?
tldr; No, it is not possible to connect to SQL Server 'directly' from browser-JavaScript1.
JavaScript can "speak" HTTP and WebSockets, but SQL Server "speaks" TDS. To communicate there needs to be a common medium/protocol that both the client and server use.
While there are WebSocket proxies that technically make this possible it still requires a separate proxy service (and you'd still have to write/find a JavaScript TDS driver). I don't recommend eliminating the controlled access layer.
Likewise, an HTTP proxy where raw SQL commands are sent to/from the client could be used. I wouldn't advise this either, but some do exist.
External code/libraries (eg. ActiveX, Java) could establish the SQL connection and proxy through to the JavaScript client.
In all of these cases there is an intermediate helper and browser-JavaScript never connects 'directly'.
1 JavaScript is a language and this answer focuses on a browser implementation with browser-supported libraries/functions. One could argue that using node modules would still 'be JavaScript', and they would be correct .. in a different environment.
You cannot establish a direct connection to a database from a client's web browser. What you will need to do is create a server side application to expose an API for getting the data over HTTP.
Take a look at Microsoft's ASP.NET Web API
Sort of
You could create an endpoint that is a wrapper for stored procedure(s) that takes the procedure name as a parameter, as well as the parameters for the procedure.
Once you have such a mechanism in place, you can create endpoints that expose procedures automagically.
http://yourserver/services/yourprocname?prm1=val,prm2=val,etc
If you feel really ambitious you can try out SQL 2016 and return JSON directly from those procedures. Then you can nest data using subqueries and return the JSON in a single payload. No serialization, no objects, just read the data and return it.
< 2016 you could put the results into a Dictionary and use NewtonSoft to serialize it. Assuming you are returning flat data you'd be good to go. Just use a reader and get the meta data from the column names for the key, and the value as object. NewtonSoft will convert that into JSON for you.
If you are returning hierarchical you could (by convention) create a series of runners that take the reader, and pump it into a Dictionary where object is another Dictionary Again the Newtonsoft stuff will help you out with the serialization.
Hope this helps, we are using this approach with 2016 and it is very nice to be able to create a stored procedure and call it without any middle tier code, deployment, etc. It just works.
Hope this helps.
Yes, you can connect to SQL Server from Client side directly by using the WebAssembly. You can write your function that calls the SQL Server in C or C++ first. Compile it to .Wasm by Emscripten compiler. Then you can call the C or C++ code by using JavaScript. In future, you should be able to do that with C# but Microsoft just started work on that. I am writing a post about it, and I will share it when it's ready. Now just because you can do it, doesn't mean you should because of security issues. But I am not here to give a lecture about what you should or should not do.
