Create an ASP SQL Stored Procedure

Thanks! Tried the example on the MS site but this worked first time.

i have this problem that i havent been able to solve for almost a week now.....
i am designing this SQL server backend & ASP frontend software....
how do i update several fields at a time concurrently from the ASP??? the update query needs a primary key for the row to be selected but that primary key is being generated by SQL server as a counter.....so i am just defining the variable where this primary key is being stored. <%=rs(call_id))%> call_id being 1,2,3... upon generation

how do i select attributes pertaining to a particular call_id so that i make changes in that row upon pressing the save button?

I have a syntax question:

how can i pass multiple variables into the example above. For instance my stored procedure will look something like Select * from myTable Where column1 = @variable1 and column2 = @variable2

........
I can't get the syntax in the .asp page correct. I've tried


Connection.Execute "myStoredProc" & variable1, & variable2

and

Connection.Execute "myStoredProc" & variable1 & variable2

and

Connection.Execute "myStoredProc" & variable1 & variable2 &


and countless others.


Any suggestions? Thanks!

Hi atlwebguy,

Please try to include space between the stored proc name and the parameters. like
this:

Connection.Execute "MystoredProc " & variable1 & "," & variable2

if your variable1 is a string parameter try this:
Connection.Execute "MystoredProc '" & variable1 & "'," & variable2

If both parameters are string make the second parameter as string also .


regards,

does this work with mySQL aswell?

Xenova-

MySQL supports stored procedures only in the newest version, but it doesn't support transactions or transactional integrity unless you use berkeleydb or innodb, both of which are not free. The stored procedure support in MySQL is basically a way to store statements, and provides no speed enhancements or compilation at all. Quite the opposite in fact, the manual suggests that the stored procedures will typically take LONGER to execute. This is not the case with commercial RDBMS packages- MSSQL will always be able to execute stored procedures faster.

To answer your question- "yes." But don't bother.

jeremy

>>how do i update several fields at a time concurrently from the ASP???

You can pass multiple parameters, like this:

create proc SX_Update_Employee
(@employeeid int, @firstname varchar(20), @lastname varchar(20),@phone varchar(10))
AS

update employee set firstname=@firstname,lastname=@lastname,phone=@phone where employeeid=@EmployeeID

go


(the employeeid int field is the primary key.)


jeremy

PS- be sure that when you call this from your asp that you fix single quotes (apostrophies) like this:

set db=createobject("ADODB.Connection")
db.open "dsnName","user","password" 'dsn-example, dsnless will work as well
sql = "SX_Update_Employee " & employeeid & ","
sql = sql & "'" & replace(firsname,"'","''") & "',"
sql = sql & "'" & replace(lastname,"'","''") & "',"
sql = sql & "'" & replace(phone,"'","''") & "'"
db.execute sql
db.close 'always clean up after yourself
set db=nothing

*note that doing the sql=sql& part is extremely bad practice and will result in poorly performing execution. I only broke it out that way for clarity in posting!

how to send error message to client throw stroed procedure in asp

how to update procedure using asp script