asked on

Some simple questions...

All this time I have been writing queries in ASP pages to access data in my d/b (2-tier architecture). The question though is: If I fill my MS SQL database with stored procedures to do the same work, how do I call directly a SP from my ASP file?

E.g. I have three SPs

SP_delete_user (user_id) -> Returns 1/0
SP_add_user (name, surname, tel_no) -> Returns user_id
SP_update_user_details (name, surname, tel_no) -> Returns 1/0

how would I call them directly from my ASP ( this is again 2-tier architecture)...

jitganguly

Follow what is described here
http://www.aspfree.com/authors/adrian/returnvalue.asp

If you happen to get errors/problems let us know

Also see for other examples

http://www.aspin.com/func/search?tree=aspin&qry=stored+procedure&cat=

Wouter Boevink

include adovbs.inc or


Function SP_add_user(strname, strsurname, strtel_no)
Dim Conn : Set Conn = server.CreateObject("ADODB.Connection")
Dim cmd : Set cmd = Server.CreateObject("ADODB.Command")
Dim RETURN_VALUE : RETURN_VALUE = Null

Conn.ConnectionString = Application("Cnn_ConnectionString")
Conn.CursorLocation = adUseClient
Conn.Mode = adModeReadWrite
Conn.Open

cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_add_user"

cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0, RETURN_VALUE)
cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 20, strname)
cmd.Parameters.Append cmd.CreateParameter("@surname", adVarChar, adParamInput, 20, strsurname)
cmd.Parameters.Append cmd.CreateParameter("@tel_no", adVarChar, adParamInput, 16, strtel_no)

cmd.Execute

SP_add_user = cmd.Parameters("RETURN_VALUE").Value

Set cmd = Nothing
'Conn.Close
'Set Conn = Nothing
End Function

Dim ReturnValue
ReturnValue = SP_add_user("test","test","1234567890")

bruno

wow, that looks much harder than anything i've ever done...

i've always just written a SQL string that calls the SP, and everything else on the page is the same as if it were a normal SQL string...

here is an example of a very simple one..

SQL = "SP_QUESTION "
SQL = SQL & "@ACTION = 'del', "
SQL = SQL & "@QUESTION_ID = " & QUESTION_ID

a little bigger:

SQL1 = "SP_QUESTION "
SQL1 = SQL1 & "@ACTION = 'upd', "
SQL1 = SQL1 & "@QUESTION_ID = " & QUESTION_ID & ", "
SQL1 = SQL1 & "@QUESTION = '" & QUESTION & "', "
SQL1 = SQL1 & "@SHOW_RESULTS = " & results & ", "
SQL1 = SQL1 & "@TYPE_ID = " & TYPE_ID

Wouter Boevink

I know it seems a lot of code, when it can be accomplished in a few lines.

Doing it this way, it's always correct you always have the right parameters and types. It's much faster.

I wrote a script which takes a stored procedure and generates the function.

ASKER CERTIFIED SOLUTION

jekl2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsmckenzie

I don't know if those are the real names of your SPs, but you shouldn't name custom stored procedures starting with "sp_" because that's the prefix for SQL Server system stored procedures. SQL Server thinks it's a system stored procedure and attempts to find it in the master db first. The time it takes SQL Server to search through the master db (instead of going directly to your SP) can potentially cause a decrease in performance.

dinosaurus

ASKER

I think this is the simplest way. As far as the naming conventions are concerned, no I am not using SP_ ... but thanks for this piece of very useful advice. Thank you all.