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

  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.