Tuesday, March 20, 2012

Can this be done with an output parameter?

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks::How could I create an output parameter that would return their new user id and then how
::would i set it to an integer variable

do you mean from the front end or inside the stored proc ?

if you mean the stored proc:


create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as

insert into ( ....) values (...) select @.userid=@.@.IDENTITY
..


HTH|||Use the OUTPUT Parameter of the Stored Procedure. Refer to SQL Server BOL for further assitance ...|||Hi yeah I mean by using the output param in the sp.

How does the @.@.Idendity work then?

How would i set this to a varialble in my front end. Would I use

Dim UserID as Integer

Dim objParam as New SqlParameter("@.@.Idendity", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objParam.Value = UserID
objComm.Parameters.Add(objParam)

Thanks|||First, you should use SCOPE_IDENTITY(), not @.@.IDENTITY.

Next, use a SP like this:


create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as

insert into ( ....) values (...)

select @.userid=SCOPE_IDENTITY()

then


Dim objParam as New SqlParameter("@.UserID", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objComm.Parameters.Add(objParam)

Then after the command is run, check objComm.Parameters("@.UserID") for the UserID|||Many thanks Douglas. We may have to start calling you superman on here lol as you come to everyones rescue.|||Thanks. I do have the glasses, though no cape, and no one wants to se me in tights<g>.

No comments:

Post a Comment