Tips: SQL Server – Constraints and Functions

If you are planning to make a table constraint that is more complex than a simple check against a field or two then I recommend using functions. By creating a function you can make complex logic by which you can return True Or False states that indicate to a constraint if the data is what you need it to be. You can also add parameters to a function giving you the dynamic possibility to process data being processed.

Example:

Lets say that you have function that wants to check if a ID value is a certain numeric ID and then you want to make sure that a row being inserted does not have a NULL value in a certain field.

Here is some sample code:

CREATE FUNCTION [dbo].[CheckForNullByID]

(

   — Add the parameters for the function here

   @someID int,

   @someFieldNotNULL char(11)

)

RETURNS bit

AS

BEGIN

   DECLARE @retValue bit = 0

   IF @someID = 40

   BEGIN

          IF @someFieldNotNULL IS NOT NULL

          BEGIN

                 SET @retValue = 1

          END

          ELSE

                 SET @retValue = 0

   END

   ELSE

          SET @retValue = 1

   RETURN @retValue

END

GO

Then you call the function in your constraint like the following expression:

([dbo].[CheckForNullByID]([rowIDValue],[someFieldValueThatIsNotToBeNULL])>(0))

Thats it :)!!!

Hope this helps 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s