SQL Server : stored procedure or function

Question

I have a table with 2 columns (A as bool and B as text), these columns can be:

both are null
if A is False, then B should be null
if A is True, then B should be not null

There are rules. I want to create a stored procedure or function to check these rules when row adding or updating (via trigger). What is better, stored procedure or function? If function, which type? In general, which variant is the best (return boolean or other way etc)?

You can alter my example. to pass in A and B (values) to the UDF. And write your mini rule there. The trade off is performance, the UDF hit will slow down the insert or update a tad. If you're only inserting 100 rows, no big deal. If you're inserting a million rows per day, then ... ummmm, maybe not. — granadaCoder
– granadaCoder, Commented Feb 21, 2013 at 16:37

ypercubeᵀᴹ · Accepted Answer · 2013-02-21 17:19:38Z

11

I think you're after a CHECK Constraint.

Example:

ALTER TABLE Xxx
  ADD CONSTRAINT chk_Xxx 
  CHECK ( (A IS NULL AND B IS NULL) 
       OR (A = 0 AND B IS NULL) 
       OR (A = 1 AND B IS NOT NULL)
        ) ;

edited Feb 21, 2013 at 17:19

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

answered Feb 21, 2013 at 16:26

chrisb

2,2181 gold badge21 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

granadaCoder Over a year ago

I upvoted as well. For this situation, this is probably the best answer. My answer is a "possibility" if the logic becomes too complex for an "in-line" solution like this one.

granadaCoder · Accepted Answer · 2013-02-21 16:37:47Z

2

I would use a CHECK CONSTRAINT wired up to a UDF.
Here is a silly example verifying that if you insert a Person, their age will be greater than 17.

if NOT exists (select *  from sysobjects 
    where id = object_id('dbo.udfOlderThan17Check') and sysstat & 0xf = 0)
    BEGIN
        print 'Creating the stubbed version of dbo.udfOlderThan17Check'
        EXEC ( 'CREATE FUNCTION dbo.udfOlderThan17Check ( @j as smallint ) RETURNS bit AS BEGIN RETURN 0 END')
    END
GO


ALTER FUNCTION dbo.udfOlderThan17Check ( @Age smallint  )
    RETURNS bit AS
    BEGIN
        declare @exists int

        select @exists = 0

        if ( @Age IS NULL )
            BEGIN
                select @exists = 1 -- NULL VALUES SHOULD NOT BLOW UP THE CONSTRAINT CHECK   
            END

        if ( @exists = 0 )

        BEGIN

            if @Age > 17
                begin
                    select @exists = 1
                end     

            END

        return @exists
    END



GO



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[Person]
    END
GO


CREATE TABLE [dbo].[Person]
(
      PersonUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID()
    , Age smallint not null
)

GO


ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY NONCLUSTERED (PersonUUID)
GO


ALTER TABLE dbo.Person
ADD CONSTRAINT [CK_Person_AgeValue] CHECK ([dbo].[udfOlderThan17Check]( [Age] ) != 0)
GO

Here are some "tests":

INSERT INTO dbo.Person (Age) values (33)
INSERT INTO dbo.Person (Age) values (16)

INSERT INTO dbo.Person (Age) select 333 UNION select 58
INSERT INTO dbo.Person (Age) select 444 UNION select 4

select * from dbo.Person

edited Feb 21, 2013 at 16:37

answered Feb 21, 2013 at 16:29

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

6 Comments

JNK Over a year ago

Check constraint is probably the way to go for this since a UDF is not going to be very efficient for checking something like this on a large data set

granadaCoder Over a year ago

I agree that CHECK is the most likely candidate. Keep in mind, my sample IS a check contraint, but also uses a UDF. But the UDF allows some "advanced" checking if that is required, so I offered this as an option. If he can get by with a normal CHECK, than that would be the way to go.

chrisb Over a year ago

Having read the question a little better my example is kinda pointless :) and this captures it better...

JNK Over a year ago

@granadaCoder Sorry, I didn't scroll all the way through the original code.

Aaron Bertrand Over a year ago

NO NO NO. Do not use UDFs in check constraints. Just don't.

|

Collectives™ on Stack Overflow

SQL Server : stored procedure or function

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related