SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optional Parameters in Stored Procedures


Optional Parameters in Stored Procedures

Author
Message
takilroy
takilroy
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8072 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64417 Visits: 19117
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
My Blog: www.voiceofthedba.com
takilroy
takilroy
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8072 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64417 Visits: 19117
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
My Blog: www.voiceofthedba.com
takilroy
takilroy
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8072 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64417 Visits: 19117
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
My Blog: www.voiceofthedba.com
takilroy
takilroy
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search