Do we have a better way to do this?

[Guest]

Hi, friends,

I have stored procedure which will return the new userID:

CREATE PROCEDURE dbo.sp_InsertUser
@lastName VARCHAR(50),
@firstName VARCHAR(50),
@email VARCHAR(50),
@phone VARCHAR(50)
AS

BEGIN
DECLARE @newID INT

BEGIN TRANSACTION

EXECUTE @newID = sp_GetNextID 'userID'

IF @newID IS NOT NULL AND @newID > 0
BEGIN
INSERT INTO users
(userID, lName, fName, email, phone)
VALUES
(@newID, @lastName, @firstName, @email, @phone)

IF @@ERROR <> 0
GOTO Error_Exit

COMMIT TRANSACTION

SELECT @newID AS newUserID

RETURN
END

Error_Exit:
ROLLBACK TRANSACTION
SELECT 0 AS newUserID
RETURN
END

I used DataSet to get the output new userID like:
mUserID = mDataSet.Tables[0].Rows[0]["newUserID"].ToString();

My question is:
(1) Do we have to use DataSet or we can have another easier way;
(2) In above sp, can I remove
SELECT * AS newUserID
and use
RETURN @userID
instead? If yes, I do not think I can user DataSet to get this new user ID.
Then, what should I do in this case?

Thanks a lot....

 

[Miha Markic [MVP C#]]

Andrew,

Why don't you use output parameter?
CREATE PROCEDURE dbo.sp_InsertUser

@lastName VARCHAR(50),
@firstName VARCHAR(50),
@email VARCHAR(50),
@phone VARCHAR(50)

@newUserId INT OUTPUT
....


--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi, friends,

I have stored procedure which will return the new userID:

CREATE PROCEDURE dbo.sp_InsertUser
@lastName VARCHAR(50),
@firstName VARCHAR(50),
@email VARCHAR(50),
@phone VARCHAR(50)
AS

BEGIN
DECLARE @newID INT

BEGIN TRANSACTION

EXECUTE @newID = sp_GetNextID 'userID'

IF @newID IS NOT NULL AND @newID > 0
BEGIN
INSERT INTO users
(userID, lName, fName, email, phone)
VALUES
(@newID, @lastName, @firstName, @email, @phone)

IF @@ERROR <> 0
GOTO Error_Exit

COMMIT TRANSACTION

SELECT @newID AS newUserID

RETURN
END

Error_Exit:
ROLLBACK TRANSACTION
SELECT 0 AS newUserID
RETURN
END

I used DataSet to get the output new userID like:
mUserID = mDataSet.Tables[0].Rows[0]["newUserID"].ToString();

My question is:
(1) Do we have to use DataSet or we can have another easier way;
(2) In above sp, can I remove
SELECT * AS newUserID
and use
RETURN @userID
instead? If yes, I do not think I can user DataSet to get this new user
ID.
Then, what should I do in this case?

Thanks a lot....

 

[Mike Edenfield]

I have stored procedure which will return the new userID:

My question is:
(1) Do we have to use DataSet or we can have another easier way;
(2) In above sp, can I remove
SELECT * AS newUserID
and use
RETURN @userID

There are several options. If you return a single field from the
procedure, you can use the SqlCommand's ExecuteScalar() function, which
returns the first field in the first record in the result set.
Alternatively, you can use the RETURN inside the stored procedure, and
SqlCommand's ExecuteNonQuery(). You will need to add a parameter called
RETURN_VALUE to your command object, and read it's value after the
procedure returns.

--Mike