Solved

SQL Output Parameters with C#

Posted on 2013-05-30
10
377 Views
Last Modified: 2013-06-26
I have an asp.net page with C# code behind. There is a SQL Server stored procedure that I use to insert a record into a table. I want to take one of the values that gets inserted and output it so that I can then use it as a parameter in another procedure. So I inert a new user. I want to take the value inserted into the last name field and use it as a parameter in another table for updating or inserting. Is there an example of this?
0
Comment
Question by:dodgerfan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 2

Expert Comment

by:eli411
ID: 39208616
It is easier to coded in stored procedure in database side instead of using that in C#!  Post your code will help to pinpoint where we can help.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39208617
Where are you calling this 2nd sproc from:  the first sproc, or you C# code-behind?
0
 

Author Comment

by:dodgerfan
ID: 39208638
I'm calling the second procedure right after running the first, in the c# code behind. Can't really post the code. Where I have to do it does not really matter. I just want it available for the second procedure to utilize.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 39208664
The reason I ask is because if you already have the value in C# (since you passed it to the 1st sproc), then why do you need to return it from the 1st sproc? Just pass the same value that you passed to the 1st sproc to the 2nd sproc.
0
 
LVL 2

Expert Comment

by:eli411
ID: 39208741
That's exactly what I want to tell dodgerfan!  take the parameter in the stored procedures and run it inside.
0
 
LVL 2

Accepted Solution

by:
eli411 earned 250 total points
ID: 39209162
Here is my stored procedure :

Alter Procedure [dbo].[usp_UpdateServiceRequest]
(
    @serviceRequestId int,
    @Request  varchar(100)
)
as
IF EXISTS (Select serviceRequestId from ServiceRequests Where ServiceRequestID = @serviceRequestID)
begin
     Update ServiceRequests SET Request = @Request WHERE ServiceRequestId = @ServiceRequestId

EXEC usp_GetServiceRequestById  @serviceRequestId  --This is where you pass the parameter into second stored procedure

End
end


--- Hopefully this code will help you to get started
0
 

Author Comment

by:dodgerfan
ID: 39209206
My stored procedure looks like this:

alter procedure InsertUser
(
appid int,
userid int output,
creatorid int,
username varchar(100) output
)
As
Declare @Err int
Set @Err = 0
SET NOCOUNT ON
IF appid > 0
begin
Insert into Users
      (
        each field listed. . .
      )
      Select UserName, LastName, FirstName, etc for each field from table that holds temp data FROM Applications where appid = @appid

      select @UserID = SCOPE_IDENTITY(), @Err = @@ERROR
END
Return @Err
Set NoCountOff

I call that from the c# code, providing the reqiuired parameters. I need to grab the UserName field and provide that to another procedure in c# that will take it and insert into a different table. So far I have been unable to return the UserName. I hope this helps clarify some.
0
 
LVL 2

Expert Comment

by:eli411
ID: 39209357
alter procedure InsertUser
(
@appid int,
@userid int output,
@creatorid int,
@username varchar(100) output
)
As
Declare @Err int
Set @Err = 0
SET NOCOUNT ON
IF appid > 0
begin
Insert into Users
      (
        each field listed. . .
      )
      Select UserName, LastName, FirstName, etc for each field from table that holds temp data FROM Applications where appid = @appid

     -- select @UserID = SCOPE_IDENTITY(), @Err = @@ERROR
     select @@Identity =SCOPE_IDENTITY()  ????
END
Return @Err
Set NoCountOff



--------------- are you trying to retrieve the newly inserted userID from the database or you are trying to pass that @userID to next stored procedure?? ----
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39209360
OK, I'll try again. You're passing in the username:

alter procedure InsertUser
(
appid int,
userid int output,
creatorid int,
username varchar(100) output
)
...

If you're passing this value in from C#, you already have the value. Why would you need to return it? Or are you referring to the @UserID that you are selecting at the end of the sproc above?
0
 

Author Comment

by:dodgerfan
ID: 39209388
Sorry for the confusion. I want to return the username. It's inserted from one table to another, but I want to use it again for another insert. The UserID is not even needed at this point, I should probably not be returning it. So it's a stored procedure that inserting values from one table to another, and I want to take one of those values (username) and use it in another insert procedure called after this one completes.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 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