Optional Parameters in Stored Procedures

  • Hi,

    I was wondering if anyone has a solution for me. I am writing SEVERAL sprocs to update different tables with optional parameters containing the column data to update. I have decided that in order to determine if a column is to be updated with a value or if the incoming execution means to update the column with null (clearing the column), I will set a 'magic' value for the default for these optional parameters. So I have decided to use 'undefined' for varchar columns, -1 for number columns and '1/1/1900' for date columns as the 'magic' defaults. So a sample stored procedure would look like this:

    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

    This will work for me. However, keeping in mind that I will be writing SEVERAL sprocs in this manner, when we decide that the constant 'undefined' is not 'magic' enough and need to change it to something else, I will have to either A. modify every sproc and replace the 'undefined' literal with 'our new magic literal' or B. create a script to alter all sprocs and then global replace the 'undefined' literal there.

    What I really would like is a global variable to use as a default parameter and so all I need to do is change the value of the global variable. Does anyone know if this is possible? I have read that it is not, but thought I would ask on this forum anyways and maybe SQL 2012 has this capability or some equivalent?

    Thanks so much for reading my post.

    Teresa

  • You can use a lookup table to store default/control values.

    Or, less overhead but more obscure, you can store the default/control values in specific byte positions of CONTEXT_INFO.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • If you have really wide ranging items, perhaps a lookup tables makes sense.

    Personally, I never would use NULL in the field as an update. If someone wanted to remove data from a field, I'd set it to 0, or '', or something else. I know I might run into issues, but so far that's worked.

    For me, I've used NULL as the default value. That means the parameter was not passed, and I ignore it in code. If I needed to pass in NULL for that field, I might use another method, maybe a different parameter (bit) that says use the field as NULL.

  • Unfortunately, I can't use look up tables because a default value for a parameter MUST be a constant, meaning either a literal value or a @@ constant. So, I'm really looking for a way to create a global variable that can be user/database defined? If anyone knows if this is possible, please let me know. Or if there is a way to really determine if a parameter is truly passed to a stored procedure--this seems like a flaw in SQL Server.

    Thanks so much,

    Teresa

  • takilroy (7/17/2012)


    Unfortunately, I can't use look up tables because a default value for a parameter MUST be a constant, meaning either a literal value or a @@ constant. So, I'm really looking for a way to create a global variable that can be user/database defined? If anyone knows if this is possible, please let me know. Or if there is a way to really determine if a parameter is truly passed to a stored procedure--this seems like a flaw in SQL Server.

    Thanks so much,

    Teresa

    No, the lookup table is for what to compare the input paramater value to.

    Yes, the default value itself must indeed be a constant. So that part will have to change in every proc if you change the "magic" value.

    But by using the lookup table, you won't have to change any actual code in the proc.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Scott has a good idea. Can you show some pseudocode or some real code that explains how this should work for you, takilroy? It seems as though you're honing in on an idea, or have a limitation we're not aware of.

  • 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

  • I really think you're going to drive people crazy trying to follow this because no one else does it.

    Almost universal is to use NULL to mean "no value was passed in for this parameter".

    If you actually need to be able to pass in an actual NULL, you will have to use a flag of some sort, as someone mentioned earlier.

    [Personally I think SQL should have a function that would "tell" me if a parameter was actually specified or not, so I could tell "missing NULL" from "provided NULL", but, AFAIK, it doesn't.]

    If you try to use the "magic" value approach, you will have to modify the procs every time, since the default value, as you noted, MUST be a CONSTANT.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • You use NULL, as Scott mentioned.

    create PROCEDURE [dbo].[spUpdateTableA]

    @TableAPK int, --only required parameter

    @ColumnA nvarchar(50) = NULL,

    @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 is null then ColumnA else @ColumnA end,

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

    where TableAPK=@TableAPK

    COMMIT TRANSACTION

  • My intent is not to drive people crazy with this. I'm sorry you feel that way, Scott.

    My intent is to see if anyone has been able to or knows of a way to create a global constant variable or use a sql function to determine if a parameter was actually specified or now--I guess exactly what you think SQL cannot do.

    As far as using null as the default value, it would only work if we never allow the execution to pass in NULL with the intent to actually set the column value to NULL or use a separate flag for each and every update field, essentially doubling the number of parameters AND make sure that if you pass a parameter at execution to update a column that an update will only occur if you pass the corresponding flag to cause the actual update to occur.

    I will stick with the 'magic' value approach and modify the sprocs if the value is not 'magic' enough for now unless there are other folks that have a better approach.

    Thanks so much!

    Teresa

  • takilroy (7/17/2012)

    My intent is to see if anyone has been able to or knows of a way to create a global constant variable or use a sql function to determine if a parameter was actually specified or not--I guess exactly what you think SQL cannot do.

    As far as using null as the default value, it would only work if we never allow the execution to pass in NULL with the intent to actually set the column value to NULL or use a separate flag for each and every update field

    Typically if NULL explicitly needs to be set, which is luckily rather rare, I use a "special" ("magic") value that means "set this column to NULL" rather than a flag. For example, for a varchar column, 'NULL' or '=NULL', etc.. For an int column, -2147483648, i.e., the largest negative value, for a date, '19000102' (sic), etc..

    UPDATE dbo.tablename

    SET columnA = CASE

    WHEN @columnA IS NULL THEN columnA

    WHEN @columnA = 'NULL' THEN NULL

    ELSE @columnA END

    Even using NULL flags, one could use a single column to pass in all the NULL flags if desired.

    At any rate, good luck with this!

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply