Prev: ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 100 ---- giving err
Next: UPDATE in UDF
From: Jeroen Mostert on 4 Jun 2010 01:55
On 2010-06-03 20:53, jonpb wrote:
> On 02/06/2010 9:42 PM, Jeroen Mostert wrote:
>> If I understand you correctly, you want to be able to execute any
>> statement and get the identity value it produced. Why? Why can't you
>> simply write the SCOPE_IDENTITY() assignment after the statement itself?
>> There seems to be little value in a stored procedure for this.
>
> Because I want a generic way of doing this

Genericity and T-SQL do not like each other well -- in general. :-)

> and it was my understanding that it needed to be in a stored procedure to
> make the "transaction" atomic.
>
Well... no. Stored procedures have nothing to do with atomicity. They do
introduce a new scope, but as you've found out, there are other ways of
doing that. And you probably don't need a new scope here in the first place.

>> You *can* do this, but not without dynamic SQL of the form I
>> demonstrated earlier. If you are going to dynamic SQL route, see
>> http://www.sommarskog.se/dynamic_sql.html for a thorough treatise.
>
> Thanks very much for the link, I obviously have fair bit to learn about
> how dynamic sql works, why it works they it does.
>
> I may actually use what you call "horrible and convoluted and not suited
> for production code" because my concern is not so much an ugly stored
> procedure but a simple client interface.
>
This actually brings up a good point -- remember that you *have* a client
side. Unless all your code is T-SQL, you are probably working with a
programming language that can execute multiple SQL statements just fine.
Writing a function/method/procdure (whatever your language has) for
executing a "SELECT SCOPE_IDENTITY()" after any other statement is trivial.
This is easier to understand and probably easier to maintain than meta-code
in T-SQL (and avoids most of the pitfalls of dynamic SQL as described in
Erland's page).

--
J.
First  |  Prev  | 
Pages: 1 2
Prev: ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 100 ---- giving err
Next: UPDATE in UDF