Haw can i send a parametre to SQL server

[ivan divandelen]

Hi, How can i pass a parameter SQL stored Procedure from ASP pages

[Bob Barrows]

There are several alternatives.

 

1. Use the technique described here: http://www.aspfaq.com/show.asp?id=2201

 

Personally, I don't like this technique since:

a. You have to worry about preventing hackers from injecting SQL into your code (there are ways to prevent this - see the SQL Injection FAQ at www.sqlsecurity.com)

 

b. You have to correctly delimit your parameter values, just as if you were creating a dynamic SQL statement (actually, that is exactly what you are doing here). You also have to correctly handle string values that contain literal characters that are normally used as delimiters. While I've done this enough times so that it is second nature to me now, in the beginning this was the largest stumbling block to my learning how to create strings containing dynamic SQL statements.

 

c. There is some performance-impairing overhead involved with both the concatenation of the SQL statement that ultimately runs the stored procedure, and the preparation of the statement on the SQL Server box, which happens before the statement is actually executed.

 

d. It forces you to return data only by recordsets: no output or return values can be used with this technique. Recordsets require substantial resources, both on the SQL Server which has to assemble the resultset and pass it back to the client, and on the web server which has to marshal the resultset and transform it into an ADO recordset. This is a lot of overhead when we're talking about returning one or two values to the client.

 

However, a lot of people do like this technique because:

a. They have no problem knowing when and how to concatenate delimiters into the SQL statement, and how to handle string parameters that contain literal characters that are normally used as delimiters

b. They have taken the necessary steps to prevent SQL Injection

c. You can assign the statement to a variable and, if there's an error during the debug process, you can response.write the variable to see the actual statement being sent to the SQL Server. If the statement has been created correctly, you can copy and paste it from the browser window into Query Analyzer and further debug it

d. They are aware of the performance hit, and consider it to be too minor to worry about. (To be fair, in many cases, this perfomance hit is relatively minor)

 

The alternatives I prefer completely eliminate objection b from above.

 

1. If you have output parameters, or you are interested in using the Return value from your procedure, use an explicit ADO Command object. Now, this can be tricky, especially if you do it the correct way (manually create the Parameters collection using CreateParameter instead of using Parameters.Refresh which involves an extra time-consuming trip to the database). However, there are many stored procedure code generators out there that vastly simplify this process, including the one I wrote which is available here: http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

 

2. The technique I use most often is the "procedure-as-connection-method" technique. With ADO 2.5 and higher, stored procedures can be called as if they were native methods of the connection object, like this:

conn.MyProcedure parmval1,...,parmvalN

This completely avoids the need to worry about delimiters, literal or otherwise. Plus it turns out that this technique also causes the procedure to be executed in a very efficient manner on the SQL Server box.

 

You can also use this technique if your procedure returns a recordset:

set rs=server.createobject("adodb.recordset")

'optionally, set the cursor location and type properties

conn.MyProcedure parmval1,...,parmvalN, rs

 

This technique does not work in ADO.Net, so if  you are considering porting to dotnet, then you should avoid this technique.

 

HTH,

Bob Barrows

"ivan divandelen" <nu...@iha.com.tr> wrote in message news:O8u2EM3U...@TK2MSFTNGP10.phx.gbl...