Calling SQLServer Proc: If nulls passed, proc changes value to '0.0'

Phil Mc

Trying to call a stored proc but some times don't want to have values
inserted in some fields.

Hi
I am rewriting a VBS script which called a stored proc in a SQL server
db. The proc takes a number of values both char and floats.

Sometime it is a requirement that the stored proc will not be given a
values for some of the floats.

This works in VBS with a Null. Like this:
With objCmd
.Parameters("@fundID").Value = strTAFundID
.Parameters("@year_d").Value = Year
.Parameters("@mnth_d").Value = Month
.Parameters("@turnover_ratio").Value =
IIf(Trim(Sheet1.Cells(i, 5)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
5)), 0, True))
.Parameters("@market_cap").Value =
IIf(Trim(Sheet1.Cells(i, 6)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
6)), 1, True))
.Parameters("@pe_ratio").Value =
IIf(Trim(Sheet1.Cells(i, 7)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
7)), 1, True))
.Parameters("@standard_deviation").Value =
IIf(Trim(Sheet1.Cells(i, 8)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
8)), 2, True))
End With
objCmd.Execute

But when I try to do the same in .NET, the value '0.0 is always
placed in the null value fields.
I have tried creating a string with the full sql statement placing
null, DBNull, blank space etc, all with the same result.

This is my latest attempt in C#:
SqlCommand cmdLoadFundStatistics = new
SqlCommand("sp_pcom_LoadFundStatistics_TEST", this.dbConn);
cmdLoadFundStatistics.CommandType = CommandType.StoredProcedure;
// fund id
cmdLoadFundStatistics.Parameters.Add("@fundID", SqlDbType.Char, 10);
cmdLoadFundStatistics.Parameters["@fundID"].Value = fundid;
// year number
cmdLoadFundStatistics.Parameters.Add("@year_d", SqlDbType.SmallInt,
10);
cmdLoadFundStatistics.Parameters["@year_d"].Value = year_d;
// month number
cmdLoadFundStatistics.Parameters.Add("@mnth_d", SqlDbType.SmallInt,
10);
cmdLoadFundStatistics.Parameters["@mnth_d"].Value = mnth_d;
// trunover ratio
cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio",
SqlDbType.Float, 10);
if(tratio != null)
cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value = tratio;
else
cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value =
System.DBNull.Value;
// market cap
cmdLoadFundStatistics.Parameters.Add("@market_cap" , SqlDbType.Float,
10);
if(cap != null)
cmdLoadFundStatistics.Parameters["@market_cap"].Value = cap;
else
cmdLoadFundStatistics.Parameters["@market_cap"].Value =
System.DBNull.Value;
ETC..............................................

I would really appreciate any help here. I cannot change the database;
pages pulling from these tables behave differently if the value
'0.0' is found as apposed to no vale present.

Many thanks (see proc below)

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.sp_pcom_LoadFundStatistics_TEST
@fundID char(10),
@year_d smallint,
@mnth_d smallint,
@turnover_ratio float,
@market_cap float,
@pe_ratio float,
@standard_deviation float
AS

IF EXISTS(SELECT 1 FROM t_mnd_pct_TEST WHERE mutl_fund_id = @fundID AND
mnth_d = @mnth_d and year_d = @year_d)
UPDATE t_mnd_pct_TEST
SET turnover_ratio = @turnover_ratio,
market_cap = @market_cap,
pe_ratio = @pe_ratio,
standard_deviation = @standard_deviation
WHERE mutl_fund_id = @fundID AND mnth_d = @mnth_d and year_d =
@year_d
ELSE
BEGIN
--Need to ensure that t_mkt record exists for fund and date due to
foreign key restriction
IF NOT EXISTS(SELECT 1 FROM t_mkt_TEST WHERE mutl_fund_id = @fundID
AND mnth_d = @mnth_d and year_d = @year_d)
INSERT INTO t_mkt_TEST(mutl_fund_id, mnth_d, year_d)
VALUES (@fundID, @mnth_d, @year_d)

INSERT INTO t_mnd_pct_TEST(mutl_fund_id, mnth_d, year_d,
turnover_ratio, market_cap, pe_ratio, standard_deviation)
VALUES(@fundID, @mnth_d, @year_d, @turnover_ratio, @market_cap,
@pe_ratio, @standard_deviation)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Feb 9 '06 #1

Chris Priede

Hi,

Phil Mc wrote:

But when I try to do the same in .NET, the value '0.0 is
always placed in the null value fields.

I'd have expected a SQL execution erorr instead -- makes me wonder what else
may be going on -- but try the following changes to your C# code and stored
procedure (only relevant parts reproduced, comments added):

// removed:
// cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio", SqlDbType.Float,
10);
//
// We can add and assign value in one line

if(tratio != null)
cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio",
SqlDbType.Float).Value = tratio;

// removed:
// else
// cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value =
System.DBNull.Value;
//
// Instead of adding parameter with DBNull.Value for Value, we don't
// add it at all.

if(cap != null)
cmdLoadFundStatistics.Parameters.Add("@market_cap" ,
SqlDbType.Float).Value = cap;
Give the same treatment to any other optional parameters. Now, on to the
stored procedure:
ALTER PROCEDURE dbo.sp_pcom_LoadFundStatistics_TEST
@fundID char(10),
@year_d smallint,
@mnth_d smallint,
@turnover_ratio float = null, /* give parameter default value of null
*/
@market_cap float = null,
@pe_ratio float,
@standard_deviation float
AS

/* whatever */

If that doesn't help, make sure you don't have default value constraints for
these columns.
--
Chris Priede

Feb 9 '06 #2