Problems with IF

Hi,

I have this stored procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[sp_insertpositionsheader]

@.settlementDate DATETIME,

@.description VARCHAR(50),

@.calculatedUsed INT = 0,

@.refRecId INT OUTPUT

AS

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

WHERE SettlementDate = @.settlementDate

IF (@.refRecId = 0)

INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed) SET @.refRecId = SCOPE_IDENTITY()

RETURN @.refRecId

I call it from my code, like this:

sqlCommand = new SqlCommand();

sqlCommand.Connection = dbConnection;

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.CommandText = "sp_insertpositionsheader";

sqlCommand.Parameters.AddWithValue("@.settlementDate", settlementDate);

sqlCommand.Parameters.AddWithValue("@.description", "MAN Settlement report");

sqlCommand.Parameters.AddWithValue("@.calculatedUsed", isCalculated);

SqlParameter refRecIdParameter = new SqlParameter("@.refRecId", SqlDbType.Int);

refRecIdParameter.Direction = ParameterDirection.Output;

sqlCommand.Parameters.Add(refRecIdParameter);

sqlCommand.ExecuteScalar();

refRecId = (int)refRecIdParameter.Value;

On the last line, I get "specified cast is not valid" error. I don't know why. And when I delete IF in the stored procedure, everything runs ok (but I get wrong number ).

Could you help me explain it?

Change the top to:

ALTER PROCEDURE [dbo].[sp_insertpositionsheader]

@.settlementDate DATETIME,

@.description VARCHAR(50),

@.calculatedUsed INT = 0,

@.refRecId INT OUTPUT

AS

SET NOCOUNT ON

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

.

.
.|||

Helps not.

Situation is still the same.

|||OHHHHH. Now that I look at it, you are missing BEGIN/END on your IF. Change it to:

SET NOCOUNT ON

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

WHERE SettlementDate = @.settlementDate

IF (@.refRecId = 0)

BEGIN

INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed)

SET @.refRecId = SCOPE_IDENTITY()

END

RETURN @.refRecId

You are running 2 statements under your IF which requires BEGIN/END. I have a rule of ALWAY using BEGIN/END after IF, even when technically unnecessary because you never know when you will want 2 statements.