StoreProcedure Nullable is false

Cybermap · June 21, 2019, 12:15pm

Hi is tere any way to set witch parameters on a ST is Nullable?

</Function>

I need to control this in th st.
Tanks.

enchev · June 21, 2019, 1:07pm

Hi,

This is not supported at the moment. You can execute your stored procedure using custom server method approach and pass desired values:

Best Regards,
Vladimir

Cybermap · June 21, 2019, 1:23pm

Thank you for the reply.

Cybermap · June 24, 2019, 2:34pm

Hi Vladimir,

Is it possible invert the situation (allow nulls by default)? Being the responsibility for the development of the SP, handling the nulls. We have more than 50 SP developed by the client that uses nulls as input and business rules.

enchev · June 24, 2019, 2:52pm

Every SP method will execute partial method to allow you to change parameters before executing the procedure:

[HttpGet]
    [EnableQuery(MaxExpansionDepth=10,MaxNodeCount=1000)]
    [ODataRoute("UspDtmsDroneAddEditsFunc(ID_DTMS={ID_DTMS},DroneModel={DroneModel},DroneManufacturer={DroneManufacturer},DroneSerialNumber={DroneSerialNumber},TrackID={TrackID},DroneType={DroneType},ExtraHTML={ExtraHTML})")]
    public IActionResult UspDtmsDroneAddEditsFunc([FromODataUri] string ID_DTMS, [FromODataUri] string DroneModel, [FromODataUri] string DroneManufacturer, [FromODataUri] string DroneSerialNumber, [FromODataUri] int TrackID, [FromODataUri] string DroneType, [FromODataUri] string ExtraHTML)
    {
        this.OnUspDtmsDroneAddEditsDefaultParams(ref ID_DTMS, ref DroneModel, ref DroneManufacturer, ref DroneSerialNumber, ref TrackID, ref DroneType, ref ExtraHTML);

        var items = this.context.UspDtmsDroneAddEdits.AsNoTracking().FromSql("EXEC [dbo].[usp_DTMSDroneAddEdit] @ID_DTMS={0}, @DroneModel={1}, @DroneManufacturer={2}, @DroneSerialNumber={3}, @TrackID={4}, @DroneType={5}, @ExtraHTML={6}", ID_DTMS, DroneModel, DroneManufacturer, DroneSerialNumber, TrackID, DroneType, ExtraHTML);

        this.OnUspDtmsDroneAddEditsInvoke(ref items);

        return Ok(items);
    }

    partial void OnUspDtmsDroneAddEditsDefaultParams(ref string ID_DTMS, ref string DroneModel, ref string DroneManufacturer, ref string DroneSerialNumber, ref int TrackID, ref string DroneType, ref string ExtraHTML);

You can use this partial method to supply needed parameter value.

Cybermap · June 24, 2019, 3:37pm

The problem is that if the parameter is null, it does something, otherwise it does something else, and the parameter can be null.
Example if a @id is null it will return all values of any id in the table, if not it returns only that id.
They have other parameters like dates that can be null.
Keep in mind that your client made the SPs (53 to be precise) and 8 are for searching.

enchev · June 26, 2019, 9:10am

So this procedure does some kind of filtering? In one case it returns one record and in the other case returns collection of records? Can you describe how the generated code from Radzen limits this case?

Cybermap · June 26, 2019, 11:19am

Hi, If you think that we can not send to the parameter the null value, the stored procedure will never return all values.

An example :

CREATE PROCEDURE uspFindToNotify
@DocumentType int
,@StartDate datetime2(7)
,@EndDate datetime2(7)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

if ((@StartDate is null) and (@EndDate is null))
Begin
 select @StartDate= min(CreateDate) From LegalDocuments where  (DocType = @DocumentType or @DocumentType is null) and DocStatus= 5 --'5' is closed
 select @StartDate= Max(DateStatus) From LegalDocuments where  (DocType = @DocumentType or @DocumentType is null) and DocStatus= 5 --'5' is closed
end 
else if (not (@StartDate is null) and (@EndDate is null))
Begin
	set @EndDate = EOMONTH ( @StartDate )
end 
else if ((@StartDate is null) and not(@EndDate is null))
Begin
	set @StartDate  =   DATEADD(yy, DATEDIFF(yy, 0, @EndDate), 0)
end 

-- Insert statements for procedure here
SELECT * from Documents as D
	inner join LegalDocuments as L on L.DocID = D.Id
	where L.CreateDate between @StartDate and @EndDate and (L.DocType = @DocumentType or @DocumentType is null) and L.DocStatus= 5 and D.DocStatus in (1,2,5)

END
GO

as you can see they have rules on the dates and type of document

enchev · June 26, 2019, 12:55pm

I see. At the moment the generated code will attempt to parse the date from the parameter and null is not allowed. We will do our best to enable such scenarios in our next release.

Cybermap · June 26, 2019, 2:09pm

Thank you for the reply.

enchev · June 27, 2019, 6:34am

Hi,

I've replied to your last email with private build. When inferring the data-source now, Radzen will read stored procedure parameters meta to check if the parameter is nullable or not. The execution can handle null as well.

Let me know how it goes!

Best Regards,
Vladimir

Cybermap · June 27, 2019, 9:33am

Hi,

It gives the me a error

With a New Project or existing project doesn't matter.

Best Regards

enchev · June 27, 2019, 9:50am

Hi,

What is your SQL Server version? You can execute SELECT SERVERPROPERTY('ProductVersion') to check it. I have 14.0.2014.14 which is SQL Server 2017. According to MSDN is_nullable should be part of sys.parameters since SQL Server 2008 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-parameters-transact-sql?view=sql-server-2017

Cybermap · June 27, 2019, 10:02am

My version is 11.0.6020.0 witch is 2012 SP3.

enchev · June 27, 2019, 10:06am

Can you execute select * from sys.parameters to check what will be the result?

Cybermap · June 27, 2019, 10:14am

object_id	name	parameter_id	system_type_id	user_type_id	max_length	precision	is_output	default_value
1435079		0	231	231	-1	0	1	NULL
1435079	@Title	1	231	231	510	0	0	NULL
1435079	@Subtitle	2	231	231	1024	0	0	NULL
1435079	@Url	3	231	231	2048	0	0	NULL
1435079	@Has_Children	4	56	56	4	10	0	NULL
1435079	@Type	5	239	239	2	0	0	NULL
1435079	@Image	6	231	231	2048	0	0	NULL
1435079	@Children	7	231	231	-1	0	0	NULL
2099048		0	56	56	4	10	1	NULL
49435250		0	231	231	-1	0	1	NULL

enchev · June 27, 2019, 10:17am

Yep, is_nullable definitely is not part of the metadata. We will try to handle this in our code and if is_nullable does not exist we will mark the parameter as nullable. I'll reply to your mail with more info.

enchev · June 28, 2019, 12:37pm

Issue resolved with just released Radzen 2.18.7.

Cybermap · June 28, 2019, 4:43pm

Please check the order of the paramters you no longer gave named parameters.