Hej
Så fandt jeg løsningen :-)
Inspireret af dette link, nederst på siden:
http://www.daniweb.com/forums/thread93386.htmlArne-inspiration
----------------
sp:
ALTER PROCEDURE [dbo].[TEST_OUTRSRET]
@outarg INTEGER OUTPUT
AS
SELECT @outarg = 123
SELECT * FROM vendors
RETURN @outarg
C-sharp-kode
-------------
public int RecievedReturnnumber(int activityID)
{
int allReturnedNumbered = 0;
SqlConnection connection = new SqlConnection(this.ConnectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TEST_OUTRSRET";
command.Parameters.Add("@outarg", SqlDbType.Int);
command.Parameters["@outarg"].Direction = ParameterDirection.Output;
command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
connection.Open();
command.ExecuteNonQuery();
allReturnedNumbered = (int)command.Parameters["@RETURN_VALUE"].Value;
connection.Close(); connection = null; command = null;
return allReturnedNumbered;
}
Her overført til mit eget problem:
-----------------------------------
Stored Procedure:
ALTER PROCEDURE [dbo].[nlx_RecievedReturnnumber]
(
@ActivityID INT,
@iReturnedStuff bit OUTPUT
)
AS
DECLARE @PartorderStatusID16 INT
DECLARE @PartorderStatus16WithReturnNumber INT
SET @PartorderStatusID16 =(
SELECT COUNT(*)
FROM PartOrderStatus INNER JOIN PartOrders ON PartOrderStatus.PartOrderStatusID = PartOrders.PartOrderStatusID
WHERE ActivityID = @ActivityID AND PartOrders.PartOrderStatusID = 16
)
SET @PartorderStatus16WithReturnNumber = (
SELECT COUNT(PartVendorReturnReferenceNumber)
FROM PartOrderStatus INNER JOIN PartOrders ON PartOrderStatus.PartOrderStatusID = PartOrders.PartOrderStatusID
WHERE ActivityID = @ActivityID AND PartOrders.PartOrderStatusID = 16
)
IF (@PartorderStatus16WithReturnNumber - @PartorderStatusID16 = 0)
BEGIN
SET @iReturnedStuff = 1
END
ELSE
BEGIN
SET @iReturnedStuff = 0
END
RETURN @iReturnedStuff
c-sharp-koden
--------------
public bool RecievedReturnnumber(int activityID)
{
SqlConnection connection = new SqlConnection(this.ConnectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "nlx_RecievedReturnnumber";
command.Parameters.AddWithValue("@ActivityID", activityID);
command.Parameters.Add("@iReturnedStuff", SqlDbType.Bit);
command.Parameters["@iReturnedStuff"].Direction = ParameterDirection.Output;
command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
connection.Open();
command.ExecuteNonQuery();
bool allReturnedNumbered = Convert.ToBoolean(command.Parameters["@RETURN_VALUE"].Value);
connection.Close(); connection = null; command = null;
return allReturnedNumbered;
}
Det lader til at løsenet var "@RETURN_VALUE". Jeg ved ikke om jeg har forstået ExecuteNonQuery() rigtigt; ser ud til at returnere antallet af rækker. Dvs - i dette tilfælde ikke at gøre noget "andet" end at exekverer SP. Er det en rigtig antagelse?
Jeg ved ikke om du vil ha' point?