Solved

SQL Output Parameters with C#

Posted on 2013-05-30
10
366 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 74

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
 
LVL 74

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 74

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

16 Experts available now in Live!

Get 1:1 Help Now