Sql – Passing variables to stored procedure with Classic ASP

asp-classicsqlstored-procedures

Ok so I am trying to pass some string variables from a classic ASP page to an MSSQL2000 db thusly:

strSQL = "exec UpdateEvent " & xID & ",'" & xEventID & "'," & xEventDisplayName & "," & xEventType & "," & xEventStatus & "," & xStartDate & "," & xEndDate & "," & xSurveyTemplateID & ""

Yet I end up with the error (including writing out the strSQL contents):

exec UpdateEvent 1,'1-44KTDL',,,,,,

Microsoft OLE DB Provider for SQL
Server error '80040e14'

Line 1: Incorrect syntax near ','.

/eventedit.asp, line 225

Now I am not sure if it is the dash in the EventID variable that is causing my problems (or why all the other variables are coming up with null values when there is data there…) . I have tried many many combinations of quotes and tics to appease the syntax interpreter but to no avail. What am I doing wrong? Is there a better way to do this simple stored procedure call?

Best Solution

That's very VERY bad; your code is subject to SQL injection attacks and needs to be fixed as soon as possible.

<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
<%
Set cmd = Server.CreateObject("ADODB.Command")
' ... open connection and stuff ... '
cmd.CommandText = "UpdateEvent"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

cmd.Parameters(1) = xID 
cmd.Parameters(2) = xEventID 
cmd.Parameters(3) = xEventDisplayName 
cmd.Parameters(4) = xEventType 
cmd.Parameters(5) = xEventStatus 
cmd.Parameters(6) = xStartDate 
cmd.Parameters(7) = xEndDate 
cmd.Parameters(8) = xSurveyTemplateID
cmd.Execute
%>

Related Solutions

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Sql – a stored procedure

Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples.

To create a stored procedure the syntax is fairly simple:

CREATE PROCEDURE <owner>.<procedure name>

     <Param> <datatype>

AS

     <Body>

So for example:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection.

Stored procedures do come with downsides, basically the maintenance associated with your basic CRUD operation. Let's say for each table you have an Insert, Update, Delete and at least one select based on the primary key, that means each table will have 4 procedures. Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probably will.

This is where using an ORM or some other method to auto generate your basic CRUD operations has a ton of merit.

Best Solution

Related Solutions

Sql-server – Select columns from result set of stored procedure

Sql – a stored procedure

Related Question