Solved

SQL Output Parameters with C#

Posted on 2013-05-30
10
374 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 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