Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-10 : 07:58:52
|
| Tony writes "I am new to SQL Server (but experienced with Oracle). I have recently started to work on an ASP application that can work with either a SQL Server database or an Oracle database.This application is full of SQL statements like:strSQL = "SELECT id,name FROM emp WHERE id=" & intIDstrSQL = "UPDATE emp SET name='" & strName & "' WHERE id=" & intIDNow I know that from Oracle's point of view, this is criminal - every SQL statement is different, never reused, etc. I would propose to change the SQL to use bind variables, i.e.:strSQL = "SELECT id,name FROM emp WHERE id=?"strSQL = "UPDATE emp SET name=? WHERE id=?"... passing the actual values via parameters.However, I am not clear on what difference (if any) this will make to SQL Server. I would not want to make the change only to find that this is a BAD approach for SQL Server! Will the performance of SQL Server:a) be improved (like Oracle)b) be about the samec) be WORSE for some reason?I have searched this site and others, but can find little reference to using bind variables (maybe because they are called something else by SQL Server?)Thanks." |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-02-10 : 09:06:54
|
| Hi Tony!I cannot answer your question about bind variables.The best way (performance- as well as security wise) is to use stored procedures and the ADO command object. I would advice you to try that.Regards,Andraax |
 |
|
|
andrewst
Starting Member
2 Posts |
Posted - 2003-02-11 : 09:20:48
|
quote:
Hi Tony!I cannot answer your question about bind variables.The best way (performance- as well as security wise) is to use stored procedures and the ADO command object. I would advice you to try that.Regards,Andraax
Thanks, Andraax. Unfortunately stored procedures are not an option at this stage (though I agree with you and would use them normally!) The application has to work with any of Access, SQL Server or Oracle. It is full of SQL select statements that have to work against any of the three DBMSs. I would have to write a stored procedure for each database, for each statement (assuming that can even be done in Access). For now, I have changed the SQL to use prepared statements with bind variables in one part of the application, and will test it against all three DBMSs to ensure it at least still works. If there are no problems, I will change the rest of the application as and when I can.I know that this will benefit Oracle massively. Hopefully it will also help SQL Server, or at least do no harm!- Tony Andrews |
|
|
|
|
|
|
|
|
Bind variables in SQL