John D John D - 3 months ago 20
ASP.NET (C#) Question

Can I reuse an INPUT parameter within the stored procedure?

Using SQL Server 2012 within asp.net webforms application, built in VS2012 Express for Web...

In my stored procedure, I have an input parameter

@UID_LOGIN AS INT
. This value may be ZERO at the time of the call.

CREATE PROCEDURE [dbo].[uspUpdateFromDetailsView]
@UID_CONTACT INT,
@UID_LOGIN INT,
@UID_USER_TYPE INT, -- etc...not shown here...


When this value is ZERO, a new LOGIN record is created and the
SCOPE_IDENTITY()
is used to fetch the new
UID_LOGIN
value as follows:

-- Retain the key to the inserted-row.
SET @UID_LOGIN = SCOPE_IDENTITY();


Can I be assured from your answer that this new value of
@UID_LOGIN
remains local to the stored procedure and does NOT change the value of the parameter.

Thanks in advance... John

Answer

Yes, @UID_LOGIN will remain = SCOPE_IDENTITY() within the BATCH until you set it to something else. Each time the procedure is executed you will have to pass in a new @UID_LOGIN because you have not given it a default value in your declaration. i.e. CREATE PROCEDURE [dbo].[uspUpdateFromDetailsView] (@UID_LOGIN = NULL) AS...

You can test snippets like this.

-- Comment out the create procedure part
--CREATE PROCEDURE [dbo].[uspUpdateFromDetailsView]
--( @UID_CONTACT               INT,
--  @UID_LOGIN                 INT,
--  @UID_USER_TYPE             INT
--)

--declare your variables
declare @UID_CONTACT INT, @UID_LOGIN INT, @UID_USER_TYPE INT

--set your variable
SET @UID_LOGIN = SCOPE_IDENTITY()

--do some stuff
INSERT INTO TableA (LOGIN) VALUES @UID_LOGIN

--check variable
SELECT @UID_LOGIN