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

Retrieve the Identifier of the newly added row

Howdie y'all,

I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one...

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32)
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
GO

I actually would like to get value of the UserId column for the newly added record.

Can anyone of you folks help me with this?

Cheers,

Wes

UserID being a primary key and identity column? For example modifying the proc as follows

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32),
@.NewID int OUTPUT
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
SET @.NewID = SCOPE_IDENTITY()
GO

This way you can get it via output parameter after the query is executed.

|||

Thank for this fast answer! It works like a charm now!

Cheers,

Wes

|||And to tag onto Teemu's response...
You might find a function called @.@.IDENTITY that seems to return thesame kind of information. Don't be tempted to use it, however. Itreturns the last identity value added with a wider scope thanSCOPE_IDENTIY(). It's appropriate only in very rare situations, andnever in the scenario you are using.
Don
|||

Funny you mention the @.@.Identity... I first tried that. I found it on msdn but for some reason I didn't trust it so I decided to ask the question to people who know what they are doing instead of just copy paste the code.

I'll start my mcsd course soon now and will follow the path of sql-server. I think I'll start a website where people can get extensive info on how to create and enter stored procedures on sql-server to become an MVP. I've found it hard to get some good information. I see a lot of differences in approach and syntax( should I use OUTPUT or just OUT or are they the same?) and no real good tutorial on where to start and how to learn with explanation.

Thanks again you both,

Wesley

Retrieve id of newly added row in stored procedure

Hi,

I am trying to return the id of the newly added row after the insertstatement in my stored procedure. I was told to "RETURNSCOPE_IDENTITY()", which i did. The problem is i do not know how to getthis value in asp.net?

I added this code below in my business layer. myDAL refers to an objectof my DAL layer. In my DAL layer, i have a addPara() method which willhelp me dynamically add as many parameters. Someone please advise me onwhat to do. Thanks

myDAL.addPara("@.RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)

Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)

)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GOTry @.@.identity

Refer :http://tinyurl.com/5sdht|||

SCOPE_IDENTITY() is definetely a more accurate function than @.@.IDENTITY. Check out Books On Line for more info. To get your code working, create an OUTPUT parameter in the stored proc:

CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)
@.MSTRPROMID INT OUTPUT
)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)
SELECT @.MSTRPROMID = SCOPE_IDENTITY()
-- Now return the InventoryID of the newly inserted record

GO

Now change your front end code to retrieve the ID

check the (2) section ofthis article for sample code.

|||

Look at the @.RETURN_VALUE parameter after you call the stored procedure.

Normally (Without any Dal stuff):

debug.print cmd.parameters("@.RETURN_VALUE).value

Where cmd is your sqlcommand/oledbcommand etc