[thelist] Using @@Identity [BLOODY WELL SOLVED}]

Ken Schaefer Ken at adOpenStatic.com
Wed Aug 24 21:45:35 CDT 2005


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Casey
: Subject: Re: [thelist] Using @@Identity [BLOODY WELL SOLVED}]
: 
: Is there anything wrong with doing it this oh-so-simple way, and
: not using a stored procedure? Occam's Razor. The simplest 
: solution is usualy the best.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you ask on any decent SQL programming forum, inline SQL will *not* be
voted the "best solution". 

a) Inline SQL is difficult to maintain. 

b) You have no abstraction (think of a sproc has having a contract with the
calling code, but the internals can be changed as required)

c) You have no granular security (you need to give you user
datawriter/datareader permissions to every table in question, whereas using
sprocs means you can just give the user "execute" permissions on the sprocs
only)

d) Using sprocs generally means that you can recreate your database at will,
since most people use CREATE PROC statements. You save all of those into a
big .sql file and run it in Query Analyser (or using osql.exe if you are
doing an unattended install or similar) to recreate an entire database.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Dim conAddDoc As SqlConnection = New
: SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MccC
: on String"))
: 
: Dim strInsert As String = "Insert MCCIME_Doctor (fname, lname, specialty,
: notes, ...) VALUES (@fname, @lname, @specality, ..) SELECT
: SCOPE_IDENTITY()"
: 
:  Dim cmdInsert As SqlCommand = NEW SqlCommand(strInsert, conAddDoc)
: 
: conAddDoc.Open
: ViewState("NewDocId") = cmdInsert.ExecuteScalar()
: conAddDoc.Close
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here you are using parameters - good. That protects you against injection.
But since you need to append all these parameters anyway, it'd just be one
extra step to remove the in-line SQL, and use a sproc instead.

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/ 



More information about the thelist mailing list