Solved

SQL Output Parameters with C#

Posted on 2013-05-30
10
372 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
  • 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now