title: Why doesn't this Check constraint work?
body: <p>I have created a check constraint in SQL Server 2005, but this check constraint doesn't work. The SQL Server Management Studio tells me by an insert statement the following message: </p>
  
  <blockquote>
    <p>Msg 547, Level 16, State 0, Line 1<br>
    The INSERT statement conflicted with the CHECK constraint "MY_CHECK_CONSTAINT". The conflict occurred in database "MY_DB", table "dbo.MY_TABLE", column 'MY_COLUMN'. </p>
  </blockquote>
  
  <p>I have added the check constraint with the following code:</p>
  
  <pre><code>ALTER TABLE MY_TABLE WITH NOCHECK
  ADD CONSTRAINT CK_MY_CHECK_CONSTRAINT CHECK (dbo.MY_FUNCTION(MY_PARAMETER)&lt;1)
  </code></pre>
  
  <p>The calling function "MY_FUNCTION" returns an int. </p>
  
  <p>My target is that if my function returns an int which is less than 1 the insert statement can successfully be completed and if the return value is bigger than 0 the insert statement has to be terminated.</p>
  
  <p>My problem now is that my function returns the value 0 but the insert statement has been terminated always. What am I doing wrong?</p>
  
  <p>The code of my function is the following:</p>
  
  <pre><code>CREATE FUNCTION MY_FUNCTION(@MY_PARAMETER uniqueidentifier) 
  RETURNS int 
  AS 
  BEGIN 
      declare @return int = 0
      SET @return = (SELECT COUNT(MY_COLUMN) FROM MY_TABLE WHERE MY_COLUMN= @MY_PARAMETER )
  return @return 
  END 
  </code></pre>
  
  <p>Thanks for your help.</p>