Monday, December 29, 2003

Calling Stored Procedures from ASP pages

I'm working on a homegrown web application with which our employees are able to both add and update some parts of our external webserver. Keywords: Intranet, SQL Server, ASP, Stored Procedure.
A SP is considered the best (most safe, flexible) way to to things to a SQL Server database. But, as far as I can see, the only way to use Stored Proc's using VBScript is a very tedious task:

you build a connectionstring (well, you'll always need a connection to your database, so I won't count this one),
spCMD.CommandText ' specify the sp
spCMD.CommandType = adCmdStoredProc 'say it's a sp
spCMD.Parameters.Append cmd.CreateParameter("@yourvar1",adInteger ,adParamInput )
spCMD.Parameters.Append cmd.CreateParameter("@yourvar2",adInteger ,adParamInput )
spCMD.Parameters.Append cmd.CreateParameter("@yourvar3",adInteger ,adParamInput
cmd.Parameters("@yourvar1") = yourvar1
cmd.Parameters("@yourvar1") = yourvar2
cmd.Parameters("@yourvar1") = yourvar3
' etc, where variables yourvar1, 2, 3... are user input (sanitized of course, to prevent SQL Injection)
spCMD.CommandTimeout = 0
spCMD.Execute ' well, execute it

you could do a spCMD.Parameters.Refresh instead, but this seems to put a load on the server. Still, I'm considering this one, because it is so much easier to code.
UPDATE: I found this site explaining a bit more on the so-called Prepared (like the example above) and Unprepared Commands. Comes in handy. However, you still have the big problem of having to change SQL code right in the middle of you asp file, in case the stored proc changes. Be it changing the variable name in the case of Prepared Commands, or the order / number of Parameters in the Unprepared Commands. I don't find this very useful, and it certainly is not really seperating the Business Layer from the Presentation Layer. And that is something you'd want to accomplish by using SP's, I'd reckon.