output parameter

Question

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?

Answer 1

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?

Answer 2

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

Answer 3

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)

Answer 4

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.