Here is my pseudocode:

create PROCEDURE [dbo].[spUpdateTableA]

@TableAPK int, --only required parameter

@ColumnA nvarchar(50) = 'undefined',

@ColumnB int = -1

as

begin

SET NOCOUNT ON

SET XACT_ABORT ON

declare @ErrorMessage nvarchar(4000);

begin try

--Start the transaction

begin transaction

update TableA

set ColumnA =case when @ColumnA = 'undefined' then ColumnA else @ColumnA end,

ColumnB=case when @ColumnB = -1 then ColumnB else @ColumnB end

where TableAPK=@TableAPK

COMMIT TRANSACTION

end try

begin catch

IF @@trancount > 0 ROLLBACK TRANSACTION

select @ErrorMessage = 'Error updating TableA. Error Message: '+ERROR_MESSAGE()

EXEC spErrorHandler @ErrorMessage

RETURN 55555

end catch

end

Whether I have to globally replace the default value or the usage of it in the sproc doesn't really matter, I'm trying to have to avoid going in to every sproc to make the change to the 'magic' value, be it the default or the usage of it.

I'd like to be able to set up some sort of global constant, one that can actually be used as a constant within the database, and reference it as such any where a constant can be used. I could for example set my magic value to @@Version and it will take it as a constant for the default parameter. I really would like a way to tell if an actual parameter was passed so that there would be a difference when the sproc is called like this:

exec [dbo].[spUpdateTableA] @TableAPK=123, @ColumnA nvarchar(50) = 'undefined', @ColumnB=555

and this,

exec [dbo].[spUpdateTableA] @TableAPK=123, @ColumnB=555

If prior to execution the record in TableA was:

TableAPK ColumnA ColumnB

123 blahblahblah 444

If I run:

exec [dbo].[spUpdateTableA] @TableAPK=123, @ColumnA nvarchar(50) =null, @ColumnB=555

I want the results to be:

TableAPK ColumnA ColumnB

123 null 555

If I run:

exec [dbo].[spUpdateTableA] @TableAPK=123, @ColumnB=555

I want the results to be:

TableAPK ColumnA ColumnB

123 blahblahblah 555

And even better (which of course this won't work without changing the magic value in my sproc to something that for sure will never be sent, which I can never guarantee):

If I run:

exec [dbo].[spUpdateTableA] @TableAPK=123, @ColumnA nvarchar(50) ='undefined', @ColumnB=555

I want the results to be:

TableAPK ColumnA ColumnB

123 undefined 555

So that the only fields that are truly updated are the ones that the calling execution wants updated.

Thanks again!

Teresa