When Using Store Proc with Enum Id with in () this braces

[incitesol]

class name : ReportRestriction

Method Name : void BuildTexts()

string.Format("({0})", (HasValue ? EnumSQLValue : "NULL"));

Error while executing Store Proc

[ariacom]

I think that restrictions for enum will not work for Stored Procedures, should be an enhamcement to do that

[incitesol]

Oh!, but it works for me if I changed the code.

from _SQLText = string.Format("({0})", (HasValue ? EnumSQLValue : "NULL"));

To, _SQLText = string.Format("{0}", (HasValue ? EnumSQLValue : "NULL"));

Any Impact if I do so?

Thanks, Sekhar Gadisala

From: Seal Report @.> Sent: 23 February 2023 04:00 PM To: ariacom/Seal-Report @.> Cc: Sekhar Gadisasala @.>; Author @.> Subject: Re: [ariacom/Seal-Report] When Using Store Proc with Enum Id with in () this braces (Issue #78)

I think that restrictions for enum will not work for Stored Procedures, should be an enhamcement to do that

— Reply to this email directly, view it on GitHubhttps://github.com/ariacom/Seal-Report/issues/78#issuecomment-1441521737, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AXEK4WSLNDW5B2ETKFSRUIDWY43ZPANCNFSM6AAAAAAVFNFLVY. You are receiving this because you authored the thread.Message ID: @.**@.>>

[ariacom]

It should work if you select only one value in your enum, but I guess this will not work if there are several values... Can you try ? If so, one idea could be to remove the () if the enum has only one value: EnumValues.Count == 1

[incitesol]

Yes your right,

If possible EnumValues with multiple '2,3,4,5,6' This makes good.

and Store Proc must allow as varchar this will allow and user can handle it inside

[ariacom]

as I can see, it is only used for the the operator 'Value Only', so it is better to remove the () as it can be controlled by the restriction text. I have done the commit, it will be in the 6.9, thank you to check.

[incitesol]

_SQLText = string.Format("{0}", (HasValue ? "'" + string.Join(",", EnumValues) + "'" : "NULL"));

this will fix Please check from your end

[incitesol]

Eric, try to change as above...

If Enum is selected multiple values it will handle via comma separated..

'1,2,3,4,8'

Pls look...

[ariacom]

Yes, but how will it work with an enum of type string ? here is what we expect: numeric 1 value -> 1 numeric several values -> 1,2,3,4,5 string or date one value -> 'aa' string or date several values -> '''aa'',''bb'',''cc'''

in this case you have to escape the ' character, can you check this point and do pull request when you are done. use the Helper.QuoteSingle() function to escape.

thanks

[incitesol]

_SQLText = string.Format("{0}", (HasValue ? "'" + string.Join(",", EnumValues) + "'" : "NULL"));

If String

DECLARE @enum NVARCHAR(400) = 'clothing,road,touring,bike'

SELECT value
FROM STRING_SPLIT(@enum, ',')

If Number DECLARE @enum1 NVARCHAR(400) = '202,2,3,40'

SELECT value
FROM STRING_SPLIT(@enum1, ',')

These to can fulfil if we place above code, I have Tested also

[ariacom]

yes but it won't work if you have a comma or a quote in a string value, can you handle this please

[incitesol]

_SQLText = string.Format("{0}", (HasValue ? "'" + string.Join(",", EnumValues.Select(s => s.Replace("'", "''")).ToList()) + "'" : "NULL"));

DECLARE @enum1 NVARCHAR(400) = '1,string''s,3'''',,4'

SELECT value
FROM STRING_SPLIT(@enum1, ',')

I tried to raise pull request for this but i cant able to push the changes into my local branch

[ariacom]

ok, I will enhance the report sample 401-SQL Server Stored Procedure with parameters to make it work for enums of type String and Numeric

[ariacom]

Ok, I have committed your changes, with dedicated tests in the report '401-SQL Server Stored Procedure with parameters' Thank you to check.