score:4

Accepted answer

A stored procedure lives in your backend database, not your ASP code. What's probably confusing you in the link you posted is that it contains the client-side code that's used to execute the server-side procedure, which isn't shown.

In your case, the actual stored procedure itself would look something like this:

CREATE PROCEDURE dbo.MyProcedure
(
   @prop1 varchar(255), --Fill in appropriate data types if necessary
   @prop2 varchar(255),
   @id int
)
AS
  SET NOCOUNT ON;

  UPDATE table
  SET prop1 = @prop1,
      prop2 = @prop2
  WHERE id = @id;

(@sgeddes is right that the procedure should include SET NOCOUNT ON. I've added that to my example.)

Then you would use the following client-side code to call that procedure (I've edited the code from your link to make it easier to follow).

'Create ADO command
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   'Set up DB connection to use, set the type of SQL command
   .ActiveConnection = db
   .CommandType = adCmdStoredProc
   .CommandText = "dbo.MyProcedure" ' Set the name of the Stored Procedure to use

   'Add a 255 character varchar parameter
   .Parameters.Append .CreateParameter("@prop1",adVarChar, adParamInput, 255)
   .Parameters("@prop1") = "Prop 1 Value"

   'Add a 255 character varchar parameter
   .Parameters.Append .CreateParameter("@prop2",adVarChar, adParamInput, 255)
   .Parameters("@prop2") = "Prop 2 Value"

   'Add a integer parameter, then pass the value of the variable userID to it
   .Parameters.Append .CreateParameter("@id",adInteger, adParamInput)
   .Parameters("@id") = userID

   'Execute the command
   .Execute
End With

'Clean up
set cmd = nothing

That's obviously a lot more code than what you have right now. So why would you do it? The most important reason is security. By creating a parameterized procedure and passing the values in parameters, you drastically reduce the likelihood of any sort of SQL injection attack.

score:0

A SQL Stored Procedure is different than an ASP Function -- a Stored Procedure is something you create in your database. The syntax is different depending on your RDBMS. But something like this:

CREATE PROCEDURE UpdateYourTable
    @id int, 
    @prop1 nvarchar(50).
    @prop2 nvarchar(50)
AS 

    SET NOCOUNT ON;

    UPDATE table 
    SET prop1 = @prop1, prop2 = @prop2 
    WHERE id = @id

GO

You would then call this stored procedure through your code very much like the article you posted.


More questions

More questions with similar tag