I am passing a parameter into a stored procedure:
param = CreateInParameter("Result", DbType.Decimal);
param.Value = testresults.Result;
cmd.Parameters.Add(param);
sometimes testresults.Result
will be null
How do I pass it in as NULL?
Here's my stored proc:
USE [SalesDWH]
GO
/****** Object: StoredProcedure [dbo].[Insert_TestResults] Script Date: 12/25/2011 23:49:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Insert_TestResults]
-- Add the parameters for the stored procedure here
@TestName varchar (500),
@Result decimal (18,4)=null,
@NonNumericResult varchar (50)=null,
@QuickLabDumpid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [SalesDWH].[dbo].[TestResults]
([TestName]
,[Result]
,nonnumericresult
,[QuickLabDumpid])
VALUES
(@TestName,@Result,@nonnumericresult,@QuickLabDumpID)
END
If testresults.Result
was not assigned a value, it just goes in as 0. How do I allow it to go into the database as NULL
?
Try this:
param.Value = testresults.Result==0?DBNull.Value:(object)testresults.Result;
Use param.Value = DBNull.Value;
to set the value in database to NULL.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With