Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Output Parameters with C#

Posted on 2013-05-30
10
Medium Priority
?
384 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

730 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