Anyone have a better alternative for constants?

SQL Server doesn't appear to have the concept of a global
or public constant. For instance, if LanguageID 1 equals
English or LanguageID 2 equals Spanish, you'd typically have
to hard code 1 or 2 in any procedure that checks this permission
first.
I've recently started using User-Defined Functions named
something like dbo.English or dbo.Spanish that do nothing
but return 1, 2, etc...
So far I haven't noticed any performance problems. Is
there a better alternative to this?
Robbe Morris - 2004/2005 Microsoft MVP C#
Earn money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.aspHave you tried asking this in the SQL-Server 2005 newsgroup?
Axel Dahmen
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> schrieb im Newsbeitrag
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||You can build a table of constants within a schema using Standard SQL
like this:
CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
.);
INSERT INTO Constants DEFAULT VALUES;
The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one row.
In full SQL-92, you can create such as table as a VIEW with a row
constructor:
CREATE VIEW Constant (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);|||I typically use a constants or properties table. If it's small and accessed
enough, it will always be in memory, and it's a heck of a lot easier to
change than a bunch of user-defined functions.
A
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||Actually this can be devastatng to performance if you use it in a FROM or
WHERE clause. It often gets called once per row, especially if you haven't
made sure that the function is deterministic: objectproperty(function_id,
'isDeterministic'). If you don't use them in a queries, It will be fine.
I would suggest you probably want to build small tables to do this sort of
thing and check the table values. Then you can use a function to get the
value if you just want one value, but you can also join to it for queries if
you need to check a value for each row.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> You can build a table of constants within a schema using Standard SQL
> like this:
> CREATE TABLE Constant
> (lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
> CHECK (lock = 'X'),
> pi REAL DEFAULT 3.141592653 NOT NULL,
> e REAL DEFAULT 2.718281828 NOT NULL,
> phi REAL DEFAULT 1.618033988 NOT NULL,
> ..);
> INSERT INTO Constants DEFAULT VALUES;
Constants = Constant|||Shock! Celko used a singular! Could it be the end of the...world? :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:OyLoV7noFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> Constants = Constant
>
>|||>> CREATE TABLE Constant
I don't like this approach because you have to alter the table when you have
a new constant.
The other approaches I like a little bit better:
(a) create a user-defined function (e.g. dbo.pi()) if it is going to be used
in a local variable but NOT in a where clause, e.g. where col <= dbo.pi()...
this is also useful if you have a set of constants that don't match in data
type.
(b) for a set of, e.g., NUMERIC constants:
CREATE TABLE dbo.NumericConstants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
Value NUMERIC(15,4)
)
This gives you great flexibility in that you can add and remove constraints
without affecting the table schema. But it ties you to a table per data
type.
(c) for a set of varying datatype constants, you can do this kind of kludge:
CREATE TABLE dbo.Constants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
NumericValue NUMERIC(15,4),
DateTimeValue DATETIME,
NVarcharValue NVARCHAR(255)
)
-- of course with a check constraint that only one can be NOT NULL
-- or add a datatype column
Or you can use SQL_VARIANT but this incurs all kinds of conversion costs
going in and out of the table.|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Shock! Celko used a singular! Could it be the end of the...world? :)
Singular was appropriate in this instance, since the table only contains one
(1) row. I simply pointed out the typo for the benefit of those who are
cutting-and-pasting. :)|||Actually this is a really interesting question and I didn't realize (you
were right :)
Strangely though, in this case the row contains multiple independent values,
but in one single row. So constants (in this case) would be the more
appropriate name, since what is represented is in fact is multiple
constants. Hmm.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:u3c9yqooFHA.3084@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Singular was appropriate in this instance, since the table only contains
> one (1) row. I simply pointed out the typo for the benefit of those who
> are cutting-and-pasting. :)
>sql

Any true public variables for custom code section?

I have been searching the newsgroup for showing the group sum in the
footer section. This isn't the same as a normal group sum as the
amount shown in the textbox is using the value straight out of the
dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
wanted to show the sum of the group amounts in the footer.
I thought I had found a solution by calling a function to store the
sum and then show the total by calling the same function with a
parameter to return the accumulated amount. Unfortunately I hit
another brick wall with this approach because even though I declared a
public variable outside the function (also tried a static within the
function), the accumulated amount only remains within the scope of
the group calling the function. When the footer section calls the same
function the accumulator is 0 instead of the accumulated amount for
the group.
My question, how do you declare and access a true public variable?
Here's an example of what is happening:
The group amount only needs to be displayed within the group, but
summed at the end of the report
Report
--
Group1 (grouped by MbGUID)
Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
Footer
Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
placeholder
Dataset columns
--
MbGUID,SizeTtl,Year,Month,Week,MaxSize
Data
--
1,120,2006,1,2,80
1,120,2006,1,3,90
1,120,2006,1,4,92
2,130,2006,1,3,87
2,130,2005,1,4,120
The output with the custom code
--
MbGuid Total
1 120
2 130
Report Total: 0
Obviously the Report Total should show 250 instead of 0.
Custom Code Section
--
Public ttl As Double = 0
Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
'Static Ttl AS Long
If pMode = "A" Then
If Ttl = Nothing Then
Ttl = 0
End If
Ttl = Ttl + pNbr
' Runtime error stating parameter is read only for the below
statement
'Report.Parameters!pSizeTtl = pNbr
AddGroupTotal = pNbr
ElseIf pMode = "T" Then
AddGroupTotal = Ttl
End If
End Function
MarkI'm also having the same problem.
I'm attempting to build a public array in the details section of a table and
then perform calculations against that, now filled array in the group footer.
The problem is that when I call my custom AddToArray code in the details
section and then return the ubound value of the array, it works fine in the
details section but when it gets to the footer the array is empty again.
Anyone have any suggestions?
"Mark" wrote:
> I have been searching the newsgroup for showing the group sum in the
> footer section. This isn't the same as a normal group sum as the
> amount shown in the textbox is using the value straight out of the
> dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
> wanted to show the sum of the group amounts in the footer.
> I thought I had found a solution by calling a function to store the
> sum and then show the total by calling the same function with a
> parameter to return the accumulated amount. Unfortunately I hit
> another brick wall with this approach because even though I declared a
> public variable outside the function (also tried a static within the
> function), the accumulated amount only remains within the scope of
> the group calling the function. When the footer section calls the same
> function the accumulator is 0 instead of the accumulated amount for
> the group.
> My question, how do you declare and access a true public variable?
> Here's an example of what is happening:
> The group amount only needs to be displayed within the group, but
> summed at the end of the report
> Report
> --
> Group1 (grouped by MbGUID)
> Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
> Footer
> Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
> placeholder
> Dataset columns
> --
> MbGUID,SizeTtl,Year,Month,Week,MaxSize
> Data
> --
> 1,120,2006,1,2,80
> 1,120,2006,1,3,90
> 1,120,2006,1,4,92
> 2,130,2006,1,3,87
> 2,130,2005,1,4,120
> The output with the custom code
> --
> MbGuid Total
> 1 120
> 2 130
> Report Total: 0
> Obviously the Report Total should show 250 instead of 0.
>
> Custom Code Section
> --
> Public ttl As Double = 0
> Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
> 'Static Ttl AS Long
> If pMode = "A" Then
> If Ttl = Nothing Then
> Ttl = 0
> End If
> Ttl = Ttl + pNbr
> ' Runtime error stating parameter is read only for the below
> statement
> 'Report.Parameters!pSizeTtl = pNbr
> AddGroupTotal = pNbr
> ElseIf pMode = "T" Then
> AddGroupTotal = Ttl
> End If
> End Function
> Mark
>