advanced code snippet search

Posted By

rengber on 07/26/09


Tagged

database sql data tsql


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


TSQL Function and Check Constraint to Ensure that a Parent Value is Present in the Table

 / Published in: SQL
 

If a "InReplyToID" is provided, it must be a valid MessageId

Expand | Embed | Plain Text 
  1. ALTER TABLE [dbo].[Message]  WITH CHECK ADD  CONSTRAINT [CK_Message] CHECK  (([InReplyToId] IS NULL OR [dbo].[CheckInReplyToMessageId]([InReplyToId])>=(0)))
  2. GO
  3. ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [CK_Message]
  4.  
  5.  
  6.  
  7. CREATE FUNCTION [dbo].[CheckInReplyToMessageId] 
  8. (
  9.     @MessageId BigInt
  10. )
  11. RETURNS bit
  12. AS
  13. BEGIN
  14.     DECLARE @RetVal bit
  15.  
  16.     SET @RetVal = (SELECT MessageId
  17.                      FROM Message
  18.                      WHERE MessageId = @MessageId)
  19.  
  20.     RETURN isnull(@RetVal, 0)
  21.  
  22. END

Report this snippet  

You need to login to post a comment.