Insert stored procedure with output parameter

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

|||

Thanks a lot!

While you posted this I solved it out using SELECT @.@.Identity

Is there any diference with the solution you gave me?

|||

Quote from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Konstantin Kosinsky wrote:

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

Insert query with nested select and parameter

hey there, i'm trying to move one record from one table to the next,
so i'm doing this by doing an insert into table, then delete from the
previous table. Only thin g is on the insert i want to include a
parameter. Can't work how to do this. Do you think i need to do
another update query and insert the parameter that way, after the
insert is done?

ALTER PROCEDURE dbo.ReturnLoan

(
@.strBarcode varchar(100),
@.strLibrary varchar(100),
@.dateReturned datetime
)

AS
INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library

)
Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHERE
Barcode = @.strBarcode
AND Library = @.strLibrary
;

DELETE FROM Loans
WHERE Barcode = @.strBarcode
AND Library = @.strLibrary ;

RETURNOn 19 Oct 2004 00:04:39 -0700, Mark wrote:

(snip)
> on the insert i want to include a
>parameter.

INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library,
SomeOtherColumn

)
SELECTBarcode,
UserID,
Date_Borrowed,
Library,
@.YourParameterHere
FROMLoans
WHEREBarcode = @.strBarcode
ANDLibrary = @.strLibrary

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert parameter to retrieve a specific chart type

Hi everyone,
Is is possible to display a specific type of chart depending on an input parameter in my data. For instance, my dataset contains a column for questiontype.
If the questiontype equals 1, I would like to display to results in a barchart. If the questiontype equals 2 I would like to display the result in a pie-chart.

IS there some kind of way to manage this problem?

thanks so far.

regard,
capsync

I am not 100% certain - but close - I do not think this can be accomplished with "out of the box" RS...
As you know - when you design the report the data from the data set populates the chart, bar, pie, etc. I have not come across a method to alternate what type of chart is displayed because you have to design the report with each chart type (bar, pie, etc...) and once these charts are added via the design wizard - they are just there and that is that...
If you get an answer I would like to know as well...
One other thing I have tried to get to happen as well --
Not display the chart based upon a parameter - but once you "design the report" with a chart it appears (as long as there is data).
So I think the answer is no and I have searched everywhere for answers to the questions.
Best Regards,
joe