Reusing Update for Insert - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Reusing Update for Insert

Author

Topic

willpost
Starting Member

4 Posts

Posted - 2008-09-14 : 21:02:07

Would the database take a significant performance hit if I replaced a traditional insert with a blank insert and then update?

It would reduce code complexity:
- Only one SQL statement to list the fields
- Only one SQL statement to test
- Insert statements are more error prone. Fields names and values are in two separate places: both have to match and it doubles the risk of a misplaced comma.

For example, replacing the old code with the new code:


'Old Insert and Update
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open Application(con)
Select Case action
  Case "insert"
    sq = "INSERT INTO tblTest (Name, Address, City, time_create) SELECT 'MyName', 'MyAddres', 'MyCity', '" & Now() & "';"
  Case "update"
    sq = "UPDATE tblTest SET Name='MyName', Address='MyAddress', City='MyCity' WHERE ID=" & id & ";"
End Select
If sq <> "" Then cn.Execute(sq)
cn.Close

'New Insert and Update
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open Application(con)
If action = "insert" Then
  sq = "SET NOCOUNT ON; INSERT INTO tblTest (time_create) VALUES ('" & Now() & "'); SELECT id=@@IDENTITY; SET NOCOUNT OFF"
  Set rs = cn.Execute(sq)
  id = rs("id")
  action = "update"
End If
If action = "update" Then
  sq = "UPDATE tblTest SET Name='MyName', Address='MyAddress', City='MyCity' WHERE ID=" & id & ";"
  cn.Execute(sq)
End If
cn.Close

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-14 : 21:13:44

You are using code that is subject to SQL injection. Search my blog for "sql injection" to find some information about this.

I don't understand why you think inserts are error prone. That doesn't make sense. Just write good code and preferably put it into stored procedures. But especially don't concatenate your inline SQL like this.

It is pointless to switch your code to what you are proposing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

willpost
Starting Member

4 Posts

Posted - 2008-09-14 : 22:39:32

This is for a prototype that parses html template files for [tablename].[fieldname] and adds or updates records, fields, or tables. This way most of the programming and database administration is done through templates that are not sent to the client browsers, and the templates can be easily swapped between IIS and Apache servers. Any field values with potentially unsafe symbols are translated to html character codes before they are added to the SQL statement. Stored procedures would add an unnecessary layer and isn't a silver bullet against SQL injection.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-14 : 23:58:38

I didn't say stored procedures prevent SQL injection.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources