asked on

Stored Procedures -- Saving returned variable and using in another procedure

I have a .NET page that executes a stored procedure. Inside that procedure, I need it to execute 2 other stored procedures. The 2 "other" stored procedures are SQL inserts, and the first one returns an INT that I want to use in the second stored procedure (see my code below).

The first insert procedure that is run inside my stored procedure is executing correctly, but it doesn't seem to be saving the returned INT variable that I need to use on the second insert procedure. What am I doing wrong? Is it syntax?

CREATE PROCEDURE spDCUserAddressCredit_Add 
 @pers_email nvarchar(100),
 @pers_password nvarchar(64)
AS
BEGIN
 SET NOCOUNT ON;
 
declare @UserId bigint

Exec @UserId = spUsers_Add @pers_email, @pers_password
Exec spUsers_AddUpdateAddress @UserId, "test", "test", "test", "test", "test"

END
GO

Open in new window

derekkromm

does the spUsers_Add actually return the userid?

so if you just run this in ssms, does it return the right id?

declare @UserId bigint
Exec @UserId = spUsers_Add 'test', 'test'
select @userid

Rajkumar Gs

Make the output from first stored procedure as OUTPUT parameter and use it in second Stored Procedure

This site has well defined sample stored procedure so that you will be more clear
http://www.aspfree.com/c/a/ASP.NET-Code/Call-Stored-procedure-from-within-another-stored-procedure-return-values/

Raj

Rajkumar Gs

For this to work
Exec @UserId = spUsers_Add @pers_email, @pers_password
spUsers_Add should RETURN some value
Is it RETURN or just SELECTing

Raj

Rajkumar Gs

To understand this, check this script

CREATE PROCEDURE uspTest
(
	@input int
)
AS
BEGIN

	-- RETURN 100 * @input
	SELECT 100 * @input
END
GO

Open in new window

CREATE PROCEDURE uspTest2
AS

BEGIN

DECLARE @name VARCHAR(20)
 EXEC	@name =  uspTest 200
 
 SELECT @name

END

GO

Open in new window

EXEC uspTest2

Open in new window

Create these two stored procedures and execute in two different ways
In first stored procedure try with

RETURN 100 * @input

Open in new window

and

SELECT 100 * @input

Open in new window

saturation

ASKER

When I run

declare @UserId bigint
Exec @UserId = spKartrisUsers_Add 'test', 'test'
select @userid

I get "192" in the first query results window and "0" in the 2nd. If I run it again I get "193" in the first results window and "0" in the 2nd. Ideas?

Rajkumar Gs

As I mentioned before, It could happen when the first Stored Procedure having SELECT statement only
You need to RETURN the integer value

Rajkumar Gs

See my sample Stored Procedures - If you run the first Stored Procedure as

CREATE PROCEDURE uspTest
(
	@input int
)
AS
BEGIN

	-- RETURN 100 * @input
	SELECT 100 * @input AS FromFirstStoredProcedure
END
GO

Open in new window

and check

Raj

Lowfatspread

note a stored procedure return code is a INT value not a BIGINT!

the purpose of the return value is to indicate the successdul execution or otherwise of the procedure ...
its not really meant for passing data...

if you want to retrieve data from a stored procedure

then either
use an output parameter variable
communicate via a Table (the stored procedure inserts to)
or via the result sets the stored procedure produces (normally the client app)

deepaprakashnr

Stored Procedures cannot have scalar value returned. instead you can use output parameter for the stored procedure and call the first stored procedure as below,
Exec spUsers_Add @pers_email, @pers_password,@UserId OUTPUT

I think it will help in solving the issue

saturation

ASKER

Getting close. I changed the type to "INT", but I still don't understand why the first query gives me
a new ID,

Exec @UserId = spUsers_Add 'test', 'test' --- returns an ID incremented by 1 each time its executed

but the 2nd query run at the same time
select @userid -- returns 0.

Why is this?

Rajkumar Gs

The value from first stored procedure is not returned to the variable @userid and it is showing the default value 0, i think

As already mentioned, I also suggest OUTPUT - http:#35024221

saturation

ASKER

Deepa, when I run

Exec spUsers_Add @pers_email, @pers_password,@UserId OUTPUT

I get ERROR "has too many arguments specified."

The spUsers_Add stored procedure (at least the insert part) is below. Does this shed any light?

INSERT INTO [tblUsers]
           ([U_EmailAddress]
           ,[U_Password]
		   ,[U_LanguageID]
			,[U_CustomerGroupID]
			,[U_DefShippingAddressID]
			,[U_DefBillingAddressID]
			,[U_CustomerDiscount])
     VALUES
           (@U_EmailAddress,
			@U_Password,
			1,0,0,0,0);
	SET @U_ID = SCOPE_IDENTITY();
	SELECT @U_ID;

Open in new window

Rajkumar Gs

Check whether the Stored Procedure have all these parameters
@pers_email, @pers_password,@UserId

I think you missed @UserID

Raj

Rajkumar Gs

This link I posted before - http:#35024221 have a sample stored procedure. It shows how to declare OUTPUT parameter and call it another stored Procedure

Raj

saturation

ASKER

Ok, I'm unfamiliar with OUTPUT parameters, so I need just a little help. I posted both the script I am running and the first stored procedure which is not passing back the ID. I'm not sure where else the OUTPUT is supposed to go inside the stored procedure?

--MY SCRIPT
declare @UserId int
Exec spUsers_Add "test@test.com", "test", @UserId OUTPUT
Exec spUsers_AddUpdateAddress @UserId, "test", "test", "test", "test", "test"


-- my user add SP

CREATE PROCEDURE spUsers_Add

	INSERT INTO [tblUsers]
           ([U_EmailAddress]
           ,[U_Password]
		   ,[U_LanguageID]
			,[U_CustomerGroupID]
			,[U_DefShippingAddressID]
			,[U_DefBillingAddressID]
			,[U_CustomerDiscount])
     VALUES
           (@U_EmailAddress,
			@U_Password,
			1,0,0,0,0);
	SET @U_ID = SCOPE_IDENTITY();
	SELECT @U_ID;

Open in new window

Rajkumar Gs

CREATE PROCEDURE spUsers_Add
(
	@U_ID INT OUTPUT
)
AS
BEGIN
	INSERT INTO [tblUsers]
           ([U_EmailAddress]
           ,[U_Password]
		   ,[U_LanguageID]
			,[U_CustomerGroupID]
			,[U_DefShippingAddressID]
			,[U_DefBillingAddressID]
			,[U_CustomerDiscount])
     VALUES
           (@U_EmailAddress,
			@U_Password,
			1,0,0,0,0);
			
	SET @U_ID = SCOPE_IDENTITY();
END
GO

Open in new window

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ravi Vaddadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.