September 22, 2013 at 11:40 pm
Is there a way to get the exact values for a constraint on a column? I need to display these values in a list of options to choose from in a webpage, but do not want to parse the expression as the result of the following query:
select sys.check_constraints.definition
from sys.check_constraints
inner join sys.columns on sys.check_constraints.parent_object_id = sys.columns.object_id
inner join sys.tables on sys.check_constraints.parent_object_id = sys.tables.object_id
where sys.tables.name = 'myTable'
and sys.columns.name = 'myColumn'
and sys.columns.column_id = sys.check_constraints.parent_column_id
Parsing the expression will be error-prone and very cumbersome. There must be a way to get the exact values of '1D', '2D', '3D', '4D', for example when constraint is created using these values:
alter table myTable add constraint CK_myColumn_Values
check (myColumn in ('1D', '2D', '3D', '4D'))
thanks for the help.
September 23, 2013 at 1:16 am
i don't see need to list definition of constraint in this case.
Restrict input in web application with validation expression,
September 23, 2013 at 2:37 am
The parsing isn't so bad if you use a pattern splitter function and all of your constrains are like this IN type.
WITH CheckConstraint ([definition]) AS (
select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'
),
PatternSplit AS
(
SELECT [definition], itemnumber, item, [matched]
FROM CheckConstraint
CROSS APPLY dbo.PatternSplitCM([definition], '['']')
)
SELECT b.Item
FROM PatternSplit a
INNER JOIN PatternSplit b ON a.ItemNumber + 1 = b.ItemNumber
WHERE a.Item='''' AND b.Item LIKE '%[a-zA-Z0-9]%';
Refer to the 4th link in my signature for the PatternSplitCM FUNCTION code.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 23, 2013 at 9:25 am
@GrassHopper, Can you be more specific on 'restrict with validation expression'? What else is there other than parsing the expression, which is the result of this definition coming from SQL query??
September 23, 2013 at 11:39 am
yghaziza (9/23/2013)
@Grasshopper, Can you be more specific on 'restrict with validation expression'? What else is there other than parsing the expression, which is the result of this definition coming from SQL query??
your goal is to validate some input value in web app.
And we say, our user may insert only value which i define (in this case 1D, 2D ...)
Well,
with restrict with validation expression i mean: create and define regular expression depending on your specification in your web page, if i enter for example 9A then show error on web page.
OR
every allowed value save in table, iniFile, global variable e.t.c and check entered value with event on control where input happens
September 23, 2013 at 3:42 pm
@GrassHopper, problem is that client (GUI) has no control over the constraints or values for data as this is read-only GUI. Data is controlled by other means and user can only get the list from dB. And it seems the way to go is either parse the expression when quering constraints, or put those constraints in a separate table and get the values directly.
@SSCrazy, the question is how to get those values you have listed in the first select statement: select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'. I'm not sure how to implement your suggestion :unsure:
September 23, 2013 at 6:17 pm
yghaziza (9/23/2013)
@Grasshopper, problem is that client (GUI) has no control over the constraints or values for data as this is read-only GUI. Data is controlled by other means and user can only get the list from dB. And it seems the way to go is either parse the expression when quering constraints, or put those constraints in a separate table and get the values directly.@SSCrazy, the question is how to get those values you have listed in the first select statement: select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'. I'm not sure how to implement your suggestion :unsure:
A pretty simple modification actually:
WITH CheckConstraint ([definition]) AS (
select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'
),
PatternSplit AS
(
SELECT [definition], itemnumber, item, [matched]
FROM CheckConstraint
CROSS APPLY dbo.PatternSplitCM([definition], '['']')
)
SELECT myColumn FROM myTable WHERE myColumn IN
(
SELECT b.Item
FROM PatternSplit a
INNER JOIN PatternSplit b ON a.ItemNumber + 1 = b.ItemNumber
WHERE a.Item='''' AND b.Item LIKE '%[a-zA-Z0-9]%'
);
Assuming that you're referring to me as SSCrazy (handle is actually Dwain.C). 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply