Do we have a better way to do this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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....
 
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)
Click to expand...
@newUserId INT OUTPUT
....


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

Andrew said:
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....
Click to expand...
 
Andrew said:
I have stored procedure which will return the new userID:
Click to expand...
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
Click to expand...

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
 
Back
Top