CREATE PROCEDURE dbo.dsp_BrokerSD_Update
 (
  @BrokerCode varchar(20),
  @BrokerName varchar(50) = NULL ,
  @Commission float = NULL ,
  @BOBrokerNumber int = NULL 
 )
AS
	IF @BOBrokerNumber IS NULL	
	BEGIN
		UPDATE Brokers 
		SET BrokerName=@BrokerName, Commission=@Commission,BOBrokerNumber=DEFAULT
		WHERE BrokerCode = @BrokerCode
		if @@ROWCOUNT = 0
	 	BEGIN
 			INSERT Brokers (BrokerCode, BrokerName, Commission)
			VALUES (@BrokerCode, @BrokerName, @Commission)
 		END
	END
	ELSE
	BEGIN
		UPDATE Brokers 
		SET BrokerName=@BrokerName, Commission=@Commission, BOBrokerNumber=@BOBrokerNumber
		WHERE BrokerCode = @BrokerCode
		if @@ROWCOUNT = 0
	 	BEGIN
 			INSERT Brokers (BrokerCode, BrokerName, Commission, BOBrokerNumber)
			VALUES (@BrokerCode, @BrokerName, @Commission, @BOBrokerNumber)
 		END
	
	END
 return 

Now as you might guess BOBrokerCode has a DEFAULT in the table (in this case 0) so if it is NULL then the the default is inserted/updated

If I do simple Update/Insert in Query Analyser I can use

INSERT Brokers (BrokerCode, BrokerName, Commission, BOBrokerNumber)
VALUES ('foo','bar','30.2',DEFAULT)

I really want to be able to do away with checking if the BOBrokerNumber is NULL

ie.

CREATE PROCEDURE dbo.dsp_BrokerSD_UpdateTest
 (
  @BrokerCode varchar(20),
  @BrokerName varchar(50) = DEFAULT ,
  @Commission float = DEFAULT ,
  @BOBrokerNumber int = DEFAULT
 )
AS

	BEGIN
		UPDATE Brokers 
		SET BrokerName=@BrokerName, Commission=@Commission, BOBrokerNumber=@BOBrokerNumber
		WHERE BrokerCode = @BrokerCode
		if @@ROWCOUNT = 0
	 	BEGIN
 			INSERT Brokers (BrokerCode, BrokerName, Commission, BOBrokerNumber)
			VALUES (@BrokerCode, @BrokerName, @Commission, @BOBrokerNumber)
 		END
	
	END
 return 



and call

EXEC dbo.dsp_BrokerSD_UpdateTest "Jonny","Jonny's Brokers",25