none
Passing null values to parameters

    Question

  • I have a stored procedure with below given code:

    -- ========================================================
    -- Description:	This procedure adds a new record or updates
    --				an existing record in the table: tw_main
    -- ========================================================
    ALTER PROCEDURE dbo.sp_CreateOrUpdateMainEntry 
    	-- Parameters for the stored procedure
    	@TaskFlag varchar(10),
    	@SerialNum bigint,
    	@schemetype nvarchar(50),
    	@schm_nm nvarchar(150),
    	@schm_code nvarchar(50),
    	@dist_nm nvarchar(50),
    	@block_nm nvarchar(50),
    	@population_2001 numeric(18, 0),
    	@supply_source nvarchar(50),
    	@approval_letter_num nvarchar(50),
    	@yr_approval numeric(4,0),
    	@sch_apr_amt numeric(18, 2),
    	@sch_waited_details nvarchar(50),
    	@schm_waited_date datetime,
    	@waited_amt numeric(18, 2),
    	@Exp_lastyear numeric(18, 2),
    	@appr_amt_2010_11 numeric(18, 2),
    	@adh_exp numeric(18, 2),
    	@exp_agnst_appamt numeric(18, 2),
    	@provided_water numeric(18, 2),
    	@schm_trans_details nvarchar(50),
    	@schm_trans_date datetime,
    	@remarks nvarchar(50),
    	@completed varchar(3),
    	@dist_code varchar(3),
    	@block_code varchar(5)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	if (@TaskFlag = 'NEW')
    		BEGIN
    			INSERT INTO tw_main
    				(schemetype, schm_nm, schm_code, dist_nm, block_nm, population_2001, supply_source, approval_letter_num, yr_approval, sch_apr_amt, sch_waited_details, waited_amt, exp_lastyear, appr_amt_2010_11, adh_exp, exp_agnst_appamt, provided_water, schm_trans_details, schm_trans_date, remarks, completed, dist_code, block_code)
    			VALUES
    				(@schemetype, @schm_nm, @schm_code, @dist_nm, @block_nm, @population_2001, @supply_source, @approval_letter_num, @yr_approval, @sch_apr_amt, @sch_waited_details, @waited_amt, @exp_lastyear, @appr_amt_2010_11, @adh_exp, @exp_agnst_appamt, @provided_water, @schm_trans_details, @schm_trans_date, @remarks, @completed, @dist_code, @block_code)
    		END
    	else if (@TaskFlag = 'UPDATE')
    		BEGIN
    			UPDATE tw_main SET
    				schemetype = @schemetype,
    				schm_nm = @schm_nm,
    				schm_code = @schm_code,
    				dist_nm = @dist_nm,
    				block_nm = @block_nm,
    				population_2001 = @population_2001,
    				supply_source = @supply_source,
    				approval_letter_num = @approval_letter_num,
    				yr_approval = @yr_approval,
    				sch_apr_amt = @sch_apr_amt,
    				sch_waited_details = @sch_waited_details,
    				waited_amt = @waited_amt,
    				exp_lastyear = @exp_lastyear,
    				appr_amt_2010_11 = @appr_amt_2010_11,
    				adh_exp = @adh_exp,
    				exp_agnst_appamt = @exp_agnst_appamt,
    				provided_water = @provided_water,
    				schm_trans_details = @schm_trans_details,
    				schm_trans_date = @schm_trans_date,
    				remarks = @remarks,
    				completed = @completed,
    				dist_code = @dist_code,
    				block_code = @block_code
    				WHERE sno = @SerialNum
    		END
    END
    
    


    While passing parameters to this stored procedure from C# application, I often get error: "Error converting datatype nvarchar to numeric" when I leave all input boxes as blank which are supposed to hold numeric values. Even though I have declared a condition that if the input box is blank pass a <NULL> value to stored procedure. My C# code is as below:

        private void SaveRecord()
        {
            int SerialNumber = 0;
            if (TaskFlag == "New")
                SerialNumber = 0;
            else if (TaskFlag == "Update")
                SerialNumber = Convert.ToInt32(Session["SerialNumber"].ToString());
    
            SqlCommand cmdTwMainEntry = new SqlCommand("sp_CreateOrUpdateMainEntry", dbConnection.cn);
    
            cmdTwMainEntry.Parameters.AddWithValue("@TaskFlag", TaskFlag);
            cmdTwMainEntry.Parameters.AddWithValue("@SerialNum", SerialNumber);
            cmdTwMainEntry.Parameters.AddWithValue("@schemetype", ddlType.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@schm_nm", txtSchemeName.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@schm_code", lblSchemeCode.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@dist_nm", ddlDistrict.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@block_nm",ddlBlock.Text);
            
            if (txtPopulation2001.Text != "")
                cmdTwMainEntry.Parameters.AddWithValue("@population_2001",Convert.ToInt32(txtPopulation2001.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@population_2001", "<NULL>");
            
            cmdTwMainEntry.Parameters.AddWithValue("@supply_source",txtSupplySource.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@approval_letter_num",txtApprovalLetterNum.Text);
            
            if (txtApprovalYear.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@yr_approval", Convert.ToInt32(txtApprovalYear.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@yr_approval", "<NULL>");
    
            if (txtApprovalAmount.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@sch_apr_amt", Convert.ToInt32(txtApprovalAmount.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@sch_apr_amt", "<NULL>");
    
            if (txtWaitedLetterNum.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@sch_waited_details",txtWaitedLetterNum.Text);
            else
                cmdTwMainEntry.Parameters.AddWithValue("@sch_waited_details", "<NULL>");
    
            if (txtWaitedDate.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@schm_waited_date",Convert.ToDateTime(txtWaitedDate.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@schm_waited_date", "<NULL>");
    
            if (txtWaitedAmount.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@waited_amt", Convert.ToInt32(txtWaitedAmount.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@waited_amt", "<NULL>");
    
            if (txtLastYearExpense.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@Exp_lastyear", Convert.ToInt32(txtLastYearExpense.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@Exp_lastyear", "<NULL>");
    
            if (txtApprovalAmount2011.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@appr_amt_2010_11",Convert.ToInt32(txtApprovalAmount2011.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@appr_amt_2010_11", "<NULL>");
    
            if (txtAdhExpense.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@adh_exp",Convert.ToInt32(txtAdhExpense.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@adh_exp", "<NULL>");
    
            if (txtExpenditureAgainst.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@exp_agnst_appamt",Convert.ToInt32(txtExpenditureAgainst.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@exp_agnst_appamt", "<NULL>");
    
            if (txtProvidedWater.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@provided_water", Convert.ToInt32(txtProvidedWater.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@provided_water", "<NULL>");
    
            cmdTwMainEntry.Parameters.AddWithValue("@schm_trans_details",txtSchTransferLetterNum.Text);
    
            if (txtSchTransferDate.Text.Trim() != "")
                cmdTwMainEntry.Parameters.AddWithValue("@schm_trans_date",Convert.ToDateTime(txtSchTransferDate.Text));
            else
                cmdTwMainEntry.Parameters.AddWithValue("@schm_trans_date", "<NULL>");
    
            cmdTwMainEntry.Parameters.AddWithValue("@remarks",txtOtherRemarks.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@completed",ddlWorkStatus.Text);
            cmdTwMainEntry.Parameters.AddWithValue("@dist_code",ddlDistrict.SelectedValue.ToString());
            cmdTwMainEntry.Parameters.AddWithValue("@block_code",ddlBlock.SelectedValue.ToString());
            cmdTwMainEntry.CommandType = CommandType.StoredProcedure;
            cmdTwMainEntry.CommandTimeout = 15;
            dbConnection.OpenConnection("Scheme");
            cmdTwMainEntry.ExecuteNonQuery();
            dbConnection.CloseConnection();
        }    
    

    I want to know how to simplify parameter passing from C# to Stored Procedure. Other than NOT NULL values, the stored procedure parameter should automatically pass NULL values to the column if blank value is passed to the parameter.

    How can I simplify my C# code and also what changes are required in the stored procedure to automatically assume NULL if no value is passed to the parameter?

    Thursday, October 27, 2011 10:45 AM

All replies

  • instead for "<NULL>", should this be DBValue.null..????

    you could try

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, October 27, 2011 10:54 AM
  • I tried and it is working, but is there any way to simplify the C# code? I want more code in the stored procedure than in C#.
    Thursday, October 27, 2011 11:34 AM
  • Hi OldEnthusiast,

    Have you tried to set default value for each parameter?

    @param1 VARCHAR(100) = NULL, 
    @param2 INT = NULL


    Shatrughna.



    • Edited by Shkumar Thursday, October 27, 2011 1:28 PM
    Thursday, October 27, 2011 1:25 PM
  • yes .. you can specify a default value of each of the sp parameters.. see the e.g 

    @TaskFlag varchar(10) =1,
    	@SerialNum bigint =null,
    	@schemetype nvarchar(50),
    	@schm_nm nvarchar(150),
    	@schm_code nvarchar(50),
    	@dist_nm nvarchar(50),
    	@block_nm nvarchar(50) =0,
    	@population_2001 numeric(18, 0),
    	@supply_source nvarchar(50),
    	@approval_letter_num nvarchar(50),
    	@yr_approval numeric(4,0),
    	@sch_apr_amt numeric(18, 2),
    	@sch_waited_details nvarchar(50),
    	@schm_waited_date datetime,
    	@waited_amt numeric(18, 2),
    	@Exp_lastyear numeric(18, 2),
    	@appr_amt_2010_11 numeric(18, 2),
    	@adh_exp numeric(18, 2),
    	@exp_agnst_appamt numeric(18, 2),
    	@provided_water numeric(18, 2),
    	@schm_trans_details nvarchar(50),
    	@schm_trans_date datetime,
    	@remarks nvarchar(50),
    	@completed varchar(3),
    	@dist_code varchar(3),
    	@block_code varchar(5)

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, October 27, 2011 1:31 PM
  • There are several problems I see by quickly inspecting this code:

    1. Don't use sp_ as custom stored procedure prefix as it's reserved for the system stored procedures.

    2. If you want to be able to pass NULL as any of the parameter (and I guess in this case ignore the parameter) then I suggest to take a look at any of the blogs I list below:

    Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
    Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
    Catch All Queries - short blog by Gail Shaw

    Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

    Option recompile Option recompile discussion thread

    3. Don't use AddWithValues in your C# code. Always use Add method and specify the type and length of the parameter explicitly to avoid conversion errors and bad performance in certain cases.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, October 27, 2011 1:47 PM
    Moderator
  • There are several problems I see by quickly inspecting this code:

    1. Don't use sp_ as custom stored procedure prefix as it's reserved for the system stored procedures.

    3. Don't use AddWithValues in your C# code. Always use Add method and specify the type and length of the parameter explicitly to avoid conversion errors and bad performance in certain cases.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    What prefix is recommended for custom stored procedures if I need to prefix them?
    Thursday, October 27, 2011 4:00 PM
  • This usually depends on organization standards. Some commonly used:

    usp_ (u stands for user-defined)

    spProcedureName (no _)

    myCompanyNamesp_ProcedureName -- this is the standard we use here

    No prefix at all.

    See also an interesting mini-discussion here

    http://forum.lessthandot.com/viewtopic.php?f=101&t=1771&p=10129&hilit=tbl#p10129

    I like Erik's ideas.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 27, 2011 4:13 PM
    Moderator
  • Other than SP_ you can use anything. as industry std, prefix SP_ used to indicate m'soft system sps

    normally usp_ to indicate user stored procedure..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, October 27, 2011 4:18 PM
  • On another note, why do you need a flag to establish whether you need to update a table row or add a new row. That's very RM/COBOL-85! In SQL Server you have a couple techniques to deal with this:

    1. Use the EXISTS keyword to determine if the row is already in the table, for example:

    CREATE PROCEDURE usp_AddTransactionInfo 
       @SerialNum bigint,
       @schemetype nvarchar(50),
       @schm_nm nvarchar(150),
       .
       .
       .
    AS
    
    IF EXISTS(SELECT * FROM tw_main WHERE sno = @SerialNum)
    BEGIN
        UPDATE tw_main...
        WHERE sno = @SerialNum;
    END
    ELSE
    BEGIN
        INSERT tw_main (sno,...) VALUES (@serialnum,..);
    END 
    
    

    2. Do a DELETE and an INSERT. In some database development spheres this concept has always caused lots of controversy, but some developers favor this approach to precisely avoid having to deal with constant code branching, hence, alledgly, improving performance.

    CREATE PROCEDURE usp_AddTransactionInfo 
       @SerialNum bigint,
       @schemetype nvarchar(50),
       @schm_nm nvarchar(150),
       .
       .
       .
    AS
    
    IF EXISTS(SELECT * FROM tw_main WHERE sno = @SerialNum)
      DELETE FROM tw_main WHERE sno = @SerialNum;
    
    INSERT tw_main (sno,...) VALUES (@serialnum,..);
    
    

    The other thing I have noticed in your code is you are assuming that your INSERT or UPDATE will succeed. In other words, I don't see any transaction and error handling, for example:

    CREATE PROCEDURE usp_AddTransactionInfo 
       @SerialNum bigint,
       @schemetype nvarchar(50),
       @schm_nm nvarchar(150),
       .
       .
       .
    AS
    
    
    BEGIN TRANSACTION;
    
    BEGIN TRY
    
       IF EXISTS(SELECT * FROM tw_main WHERE sno = @SerialNum)
       BEGIN
          UPDATE tw_main...
          WHERE sno = @SerialNum;
       END
       ELSE
       BEGIN
          INSERT tw_main (sno,...) VALUES (@serialnum,..);
       END 
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
    
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH;
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
    GO   
    

    3. Finally, you will want to find a suitable name for your stored procedure. sp_CreateOrUpdateMainEntry, leads me to believe you are thinking of this as a structured programming effort where all processes start in a main() function. Assign a name that's intuitive enough and descriptive of what the procedure will be peforming. But it all starts from your table naming convention: tw_main is certainly not representative of an entity.

     

     

     


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Thursday, October 27, 2011 6:57 PM
  • @Mariano:

    Yes I was unaware of EXISTS, but I feel there is an unnecessary overhead of re-querying the database for whether the record exists or not. Passing a flag prevents this.

    As you wrote, some developers prefer delete and insert approach. I feel it is more risky to completely get rid of the row (if it has related records in other tables) and then re-insert it. In this approach, you need to monitor the child rows in other tables as well and those records also need to be re-inserted.

     

    Friday, October 28, 2011 6:54 AM