July 16, 2012 at 10:32 am
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
July 16, 2012 at 3:38 pm
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.
July 16, 2012 at 5:04 pm
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.
July 17, 2012 at 8:41 am
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
July 17, 2012 at 9:04 am
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.
July 17, 2012 at 9:34 am
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.
July 17, 2012 at 10:13 am
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
July 17, 2012 at 10:48 am
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.
July 17, 2012 at 11:20 am
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
July 17, 2012 at 11:39 am
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
July 17, 2012 at 11:57 am
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