CODEWITHSUNDEEP
c#sql-server
Amit
Amit

Reputation:

output parameter

how to get output parameter from stored procedure of one table and by using that value how to insert data in another table which is in realation with that table?

Upvotes: 2

Views: 516

Answers (4)

Ricardo Villamil
Ricardo Villamil

Reputation: 5107

When you create your SqlParameter object, assign Output to its Direction property. Then after you execute your query, cast out the value of the parameter to a C# variable and use it as you please:

SqlParameter newId = new SqlParameter();
newId.Direction = ParameterDirection.Output;
SqlCommand cmd = new SqlCommand();
// ... build your cmd...and add your other parameters as required
cmd.Parameters.Add(newId);
cmd.ExecuteNonQuery();
int outValue = (int)newId.Value;

Then use outValue to pass it to your next SQL query.

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

You can use something like this:

CREATE PROCEDURE dbo.InsertNewImage
 @ImageTitle nvarchar(30),
 @ImageDescription nvarchar(100),
 @ImageFilename nvarchar(50),
 @ImageThumbnail image,
 @AlbumID int
AS
 Begin

  Set Nocount On

  DECLARE @ImageID int

  INSERT INTO Images(ImageTitle, ImageDescription, ImageFilename, ImageThumbnail)
  VALUES (@ImageTitle, @ImageDescription, @ImageFilename, @ImageThumbnail)

  SELECT @ImageID=@@IDENTITY

  INSERT INTO ImageInAlbum(ImageId, AlbumId)
  VALUES (@ImageID, @AlbumID)

 End

Note: Use SCOPE_IDENTITY() instead of @@IDENTITY if you're using SQL Server 2005 + (I think)

Upvotes: 0

GregD
GregD

Reputation: 7000

After your first insert you'll want something like this:

SELECT @parameter_goes_here = SCOPE_IDENTITY()

Which will give you the identity of the just entered row. Then just use that value as the parameter in your next insert.

If you need a whole example...let me know

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

an output parameter is just a value, I am not sure I understand your question. Are you talking about SQL Server 2008 Table Valued Parameters perhaps?

Upvotes: 0

Related Questions