I've recently run into an issue where my stored procedures aren't receiving value of "default" when a null value is being passed to one of the string values. My previous versioned project would pass the value of "default" to the db when a string was passed with value of "null".

//this is the call currently being made to the DB exec [uspWhateverSelect] @pnProductID=-1,@piResellerID=-1,@pnvProductName=NULL,@pnvProductAcronym=NULL

//this is the call that was made with previous projects exec [uspWhateverSelect] @pnProductID=-1,@piResellerID=-1,@pnvProductName=default,@pnvProductAcronym=default

Is there a type converter plugin for stored procedures I'm missing or is this a configuration setting within the designer that controls this functionality? I've looked all over the place, but I can't seem to find any reference to in the designer.

This project was upgraded from v1 to v2 and finally to v3.5. The DB i'm interfacing with is SQL2008.

Disclaimer: I've searched for the solution to this issue, but many of the threads are pointing to questions about the product and the way it functioned in 2005-2006.

IMOO, you should specify the default values in your proc. I don't recall whether what you said was supported or not in the past, I don't think LLBLGen would send defaults for you, so maybe this was a provider thing. Anyway you should specify the defaults in your proc parameters, or inside the proc.

Thanks for the response.

The procedure in question does have a default value, however the when the procedure is passed a value of null it uses the null value instead of the default value. The legacy code substituted null parameter values into a value "default".

I am in agreement that the stored procs should be more robust and make better use of isnull filter statement, however editing the thousands of stored procedures is not an option at this time. I need to get my LL 3.5 code to act like my 2.x code in with regard to substituting "default" for string parameters with a value of null.

I'm going to step through the 2.0 LL generated code and see how the null values where substituted. I have a feeling that it may have been a customer plugin.

I've stepped through the code and it looks like the difference is related to how sp's are currently called

I'm using the following call to test the two methods below: UspBank2Select(-1,-1,null,null, new DataAccessAdapter());

Both sections of code were taken from the RetrievalProcedure definitions for the sp. The second section of code (LL 3.5 code) was slightly modified to return a dataset.

************Good Result -- LL v2*******************************************
        //default passed -- the pnvBankName and pnvBankAcronym are passed as "default"
        public static DataSet UspBank2Select(System.Decimal pnBankID, System.Int32 piResellerID, System.String pnvBankName, System.String pnvBankAcronym, DataAccessAdapter adapter)
        {
            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@pnBankID", SqlDbType.Decimal, 0, ParameterDirection.Input, true, 18, 0, "",  DataRowVersion.Current, pnBankID);
            parameters[1] = new SqlParameter("@piResellerID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, "",  DataRowVersion.Current, piResellerID);
            parameters[2] = new SqlParameter("@pnvBankName", SqlDbType.NVarChar, 50, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, pnvBankName);
            parameters[3] = new SqlParameter("@pnvBankAcronym", SqlDbType.NVarChar, 50, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, pnvBankAcronym);

            DataSet toReturn = new DataSet("UspBank2Select");
            bool hasSucceeded = adapter.CallRetrievalStoredProcedure("[ValidbankDev].[dbo].[uspBank2Select]", parameters, toReturn);

            return toReturn;
        }
        
************END Good Result -- LL v2****************************************

//the result of this going to the DB is 
exec [dbo].[uspBank2Select] @pnBankID=-1,@piResellerID=-1,@pnvBankName=default,@pnvBankAcronym=default

************Sad Result -- LL v3.5*******************************************
        
//default not passed -- the pnvBankName and pnvBankAcronym are passed as a null
        private static DataSet UspBank2Select2(IDataAccessCore dataAccessProvider, System.Decimal pnBankID, System.Int32 piResellerID, System.String pnvBankName, System.String pnvBankAcronym)
        {
            StoredProcedureCall c = new StoredProcedureCall(dataAccessProvider, @"[ValidbankDev].[dbo].[uspBank2Select]", "UspBank2Select")
                            .AddParameter("@pnBankID", "Decimal", 0, ParameterDirection.Input, true, 18, 0, pnBankID)
                            .AddParameter("@piResellerID", "Int", 0, ParameterDirection.Input, true, 0, 0, piResellerID)
                            .AddParameter("@pnvBankName", "NVarChar", 50, ParameterDirection.Input, true, 0, 0, pnvBankName)
                            .AddParameter("@pnvBankAcronym", "NVarChar", 50, ParameterDirection.Input, true, 0, 0, pnvBankAcronym);

            DataSet toReturn = c.FillDataSet();
            return toReturn;
        }


************END Sad Result -- LL v3.5****************************************

//the result of this going to the DB is 
exec [dbo].[uspBank2Select] @pnBankID=-1,@piResellerID=-1,@pnvBankName=null,@pnvBankAcronym=null

Yes, it's different, since v3.0 now all LLBLGen DQEs use DBProviderFactories, instead of the db provider directly. If you look at the v1.x or v2.6 ORM source code you will notice that LLBLGen won't pass any kind of 'default' value to the parameter, so this has to be ado.net. In v3.5 DBProviderFactory is used, so I think that's the difference.