output from check constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

output from check constraint

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 05:00:33

I have a check constraint on a table which ensures values > 0
ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD CONSTRAINT [CK_Value] CHECK (([Value]>(0)))

I have a stored procedure which inserts into this table called by a webpage.
At the moment if the insert fails the webpage gives a general message.
If InStr(ex.Message.ToString, "CK_Value") Then
lblMessage.text="All values must be greater than zero."
End If
I would like to give more information to the user.
Is it possible to retrieve the rowids which fail the constraint?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 05:06:14

What do you mean by rowids if its failed?
Expected Row Sequence or what ?

Vaibhav T

If I cant go back, I want to go fast...

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 06:09:21

The primary key for the row which failed. The insert procedure inserts the keyfield and the value. The insert is for hundreds of rows - I would like to tell the user which row caused the failure. Is there any way to get the information by using INSERTED table?

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:13:57

Are you doing a BULK Insert?

PBUH

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 06:37:01

No just a straight forward INSERT INTO using a procedure similar to this:

CREATE PROCEDURE [dbo].[INS_Values]
@keyList varchar(Max),
AS
BEGIN
INSERT INTO
[dbo].[myTable]
(a,b,c ,[Value] )

SELECT a,b,c ,[Value]
FROM [dbo].[Table2]
where keyField in (SELECT Item FROM split(@keyList, ','))
END

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 07:54:34

But SELECT Item FROM split(@keyList, ',')) is going to return multiple rows.
One way is(which is least feasible I think) you drop the constraint in the proc let those 0 values get inserted and then query the table for 0 values to be returned to the user and then delete those 0 values using delete statement and recreate the constraint.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 08:05:31

Another way that if there is check constraint on table then
you can filter out the data > 0 to insert into the table
and data < = 0 for user information.
so that you will not get any error
and you can customized your msg for the user

because i dont think dropping and re-creating constraint
for each sql transaction is a good practice.

Vaibhav T

If I cant go back, I want to go fast...

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 08:24:54

quote:
Originally posted by vaibhavktiwari83

Another way that if there is check constraint on table then
you can filter out the data > 0 to insert into the table
and data < = 0 for user information.
so that you will not get any error
and you can customized your msg for the user

because i dont think dropping and re-creating constraint
for each sql transaction is a good practice.

Vaibhav T

If I cant go back, I want to go fast...

Why didn't I think of that ??? My solution looks so stupid.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 09:10:34

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by vaibhavktiwari83

Another way that if there is check constraint on table then
you can filter out the data > 0 to insert into the table
and data < = 0 for user information.
so that you will not get any error
and you can customized your msg for the user

because i dont think dropping and re-creating constraint
for each sql transaction is a good practice.

Vaibhav T

If I cant go back, I want to go fast...

Why didn't I think of that ??? My solution looks so stupid.

PBUH

I did not say like this that your solution is stupid solution.
Thats my opinion that
for each data manipulation command one should not change database design and recreate the design.
My opinion is not matching with your solution or opinion.
thats the thing.

but still if my way of saying disturbed or hurted you then I apologize for that.

Vaibhav T

If I cant go back, I want to go fast...

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 09:15:26

quote:
but still if my way of saying disturbed or hurted you then I apologize for that.

No man I didnt say that you mentioned it as stupid.I just felt mine looked so lame & primitive.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 09:27:43

Ok, No problem.
I already given the justification for my opinion.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources