Solved

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

Posted on 2011-03-02
20
786 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:saturation
  • 10
  • 5
  • 2
  • +3
20 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35024220
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
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024221
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
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024248
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
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024259
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

0
 

Author Comment

by:saturation
ID: 35024270
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?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024283
As I mentioned before, It could happen when the first Stored Procedure having SELECT statement only
You need to RETURN the integer value
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024288
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35024291
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)
0
 

Expert Comment

by:deepaprakashnr
ID: 35024330
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
0
 

Author Comment

by:saturation
ID: 35024337
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?

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024349
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
0
 

Author Comment

by:saturation
ID: 35024354
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

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024373
Check whether the Stored Procedure have all these parameters
@pers_email, @pers_password,@UserId

I think you missed @UserID

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024383
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
0
 

Author Comment

by:saturation
ID: 35024441
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

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35024501
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

0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 167 total points
ID: 35024510
Some of the values are not seems to be declare as parameters of the stored procedure.
Here is the updated. Correct it with your correct datatype
I mean for
      @U_EmailAddress      VARCHAR(50),
      @U_Password            VARCHAR(50),

CREATE PROCEDURE spUsers_Add
(
	@U_EmailAddress	VARCHAR(50),
	@U_Password		VARCHAR(50),	
	@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

0
 
LVL 16

Assisted Solution

by:SriVaddadi
SriVaddadi earned 167 total points
ID: 35024536
--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
(
                @U_EmailAddress <preferred data type>,
                @U_Password <Preferred data type>,
      @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
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
ID: 35026870
more like this....

 always have code to check for database errors and take some action... (i.e. report back success or failure to caller...)
--MY SCRIPT
declare @UserId int,@rc int
Exec @rc = spUsers_Add @u_email_address="test@test.com" , @u_password="test",@u_id= @UserId OUTPUT
if @rc=0 
begin
   Exec  spUsers_AddUpdateAddress @UserId, "test", "test", "test", "test", "test"
end
Else 
begin
  -- error processing
end

-- my user add SP

CREATE PROCEDURE spUsers_Add
(
                @U_EmailAddress varchar(50),
                @U_Password varchar(50),
      @U_ID INT OUTPUT
)
AS
BEGIN
declare @rc int,@err int,@rows int
set @rc=-999

      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);
  -- check for errors ie already exists, database full,...?                
    Select @rc=@@errors,@rows=@@rowcount
           , @U_ID = SCOPE_IDENTITY();
    
    Return @rc
END
GO

Open in new window

0
 

Author Closing Comment

by:saturation
ID: 35027633
You are unbelievable!   Thank you for the help!   I'm sure I will have more questions about stored procedures coming up.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now