Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Optional Parameters in Stored Procedures Expand / Collapse
Author
Message
Posted Monday, July 16, 2012 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 7:52 AM
Points: 17, Visits: 39
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
Post #1330262
Posted Monday, July 16, 2012 3:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 2,268, Visits: 3,428
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1330439
Posted Monday, July 16, 2012 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,284, Visits: 15,748
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1330455
Posted Tuesday, July 17, 2012 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 7:52 AM
Points: 17, Visits: 39
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
Post #1330800
Posted Tuesday, July 17, 2012 9:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 2,268, Visits: 3,428
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1330816
Posted Tuesday, July 17, 2012 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,284, Visits: 15,748
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.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1330833
Posted Tuesday, July 17, 2012 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 7:52 AM
Points: 17, Visits: 39
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
Post #1330867
Posted Tuesday, July 17, 2012 10:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 2,268, Visits: 3,428
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1330887
Posted Tuesday, July 17, 2012 11:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,284, Visits: 15,748
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








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1330911
Posted Tuesday, July 17, 2012 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 7:52 AM
Points: 17, Visits: 39
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
Post #1330931
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse