score:4
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
- SQL Server stored procedure for ASP classic
- ASP Classic SQL Server XML format with Stored Procedure and Parameters
- SQL Server Error executing SQL stored procedure in ASP classic [Microsoft][SQL Server Native Client 11.0][SQL Server] Error
- Calling from classic ASP to a SQL Server stored procedure that has a binary datatype input parameter
- Query to list SQL Server stored procedures along with lines of code for each procedure
- Get permissions for stored procedure in sql server 2005
- How to declare a nullable guid as an optional parameter for a C# CLR stored procedure with SQL Server
- How to set timeout for a stored procedure in SQL Server
- How do I view the definition for a stored procedure that cannot be modified in SQL Server 2008 R2?
- Which one is best View or Stored procedure in sql server for data access purpose
- sql server helper stored procedure or utility for alter table alter column IDENTITY(1,1)
- SQL Server syntax error for stored procedure
- Calling SQL Server Stored Procedure using PHP PDO from Lumen for return value gives syntax error
- if-else condition for update a table in a stored procedure in SQL Server 2005
- Create SQL Server Agent job for stored procedure with input parameter
- How do I convert UTF-8 data from Classic asp Form post to UCS-2 for inserting into SQL Server 2008 r2?
- Roles needed for executing Stored Procedure in SQL Server Authentication
- How to create a progress bar for a stored procedure while importing a XML file into SQL Server Management Studio
- SQL Server stored procedure for Counting returning 0 as the count
- SQL Server Simple example for the use of Delimiter in Stored Procedure
- SQL Server stored procedure for randomly assigning survey
- Setting default parameters for a SQL Server stored procedure
- How to use the results of a SQL Server stored procedure (scheduled to run weekly) for trending the data over time?
- Position of TRY/CATCH bloc for SQL Server stored procedure
- Sql Server Stored Procedure for adding columns to a table
- How does SQL Server utilize the query plan if the stored procedure is used for bulk select and single row select?
- Handling hashed passwords stored as varbinary in SQL Server and classic ASP
- Defining parameters for executing stored procedure - Classic ASP
- How should I handle implicit transactions for a SQL Server stored procedure being called from an Oracle database?
- SQL Server 2008 query to search all stored procedure for a specific parameter name
More questions with similar tag
- Find all stored procedures that reference another stored procedure
- how to use between operator in python?
- How to join sys.databases, sys.tables and sys.columns
- Querying XML Child Nodes with WHERE clause via SQL
- With SQL Server format date as '%Y-%m-%d %I:%M%P' / '2012-04-05 11:56am'
- Shrink and Group Column using Pivot in SQL Server
- Alternative to Delete Trigger for tracking deleted records
- Call stored procedure specifying only parameters with a value
- How to get the first record from a group of records MS sql
- SQL convert datetime and subtract hours
- sp_getapplock to synchronize concurrent access to in-memory tables
- Create Diagram in SQL Server Enterprise Manager (SQL 2000)
- How to increase timeout of DataAdapter to 3 min?
- How to display text value 'X' if number is 0.00. SQL Server, Stored Procedures
- Error when declaring parameter in stored procedure
- Multiple SELECTs in one query using same WITH
- SELECT MAX(), COUNT() returning wrong values
- SQL Hint to load entire table into RAM before execution?
- Where is the code for the EDML generated Models?
- app.config not found in SQL Server external assembly call
- Convert SQL Server .mdf file format
- create SQL Server table based on a user defined type
- Windows Phone 7 databases
- InvalidSignatureException Sending POST request to AWS Kinesis from SQL
- SSIS package to export query result to CSV does not export all the records
- Why I would create another Catalog beside the default one?
- Resolving TRY_PARSE() overflow with datetime in SQL Server 2012. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
- Performance of SQL query
- listing rows with the most recent date
- max length validation check