title: Data validation in stored procedure
body: <p>I have a stored procedure in SQL Server 2008. The job of this stored procedure is very simple - it inserts data in a table. One of the column in that table is of varchar type and I have issue with that column's data validation. It is accepting any value client code is passing into- this is expected to happen. I was told not to insert 'NEW'  into that column. Client code is mysteriously sending 'NEW' as stored procedure parameter, stored procedure is inserting that value as it is. I understand that this needs to handled by client code. But , as I am responsible for the table data - I am planning to throw exception when stored procedure will find 'NEW'. How can I achieve this ? </p>
  
  <p>This is format of my stored procedure:</p>
  
  <pre><code>ALTER PROCEDURE Object_Save
      @Id int = 0 OUTPUT              
      ,@OrderId int
      ,@OrderNumber varchar(20)
  AS
      BEGIN TRY
          IF @Id = 0
              BEGIN
                  INSERT INTO Table_Order(OrderId, OrderNumber) 
                  VALUES (@OrderID, @OrderNumber)
  
                  Set @Id = SCOPE_IDENTITY()
              END
          ELSE
              BEGIN
                  -- Update
              END
      END TRY
      BEGIN CATCH
          exec error_catch
      END CATCH
  </code></pre>