return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth