enumeration stored procedure

[hlybbi]

I am trying to make an stored procedure that can read enum values

its no problem in asp.net

http://msdn.microsoft.com/library/d.../html/frlrfsystemflagsattributeclasstopic.asp

is it possible in sql server 2000 if so can any body point me into the right direction on howto


Best regards Hlynur

http://www.xodus.net

 

[NoCoolHandle]

If what I am reading from your question is correct, I suspect that all you need is a case statement..

I.E. The Enum(eration) you are looking at is an easy way to track some piece of informtaion by assinging an integer value to it..
i.e. MultiHue.Green = 2

So.. You have a couple of options, your client would need to either have a local enumeration that evaluates to an integer, that you then pass to a stored proc,
or
you can have a stored proc that takes arguemnts in strings and cases them into a numeric value that is stored into a table as an integer...

E.G.
Create Proc IncrementDay
@day varchar(300)
as
set nocount on
If @day = 'Monday'
Begin
select 2
End
else if @day = 'Tuesday'
Begin
select 3
End

HTH

Rob

 

[SQLDenis]

Isn't joining on a lookup table better?


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hlybbi]

no what i am looking for is if i have multible choices

like if i have the number 6 then it stands for
Green,Blue
or i have the number 10 i get
Green,Red

Green = 2,
Blue = 4,
Red = 8

Best regards Hlynur

http://www.xodus.net

 

[gmmastros]

Is this what you are looking for?

Declare @White Integer
Declare @Green Integer
Declare @Blue Integer
Declare @Red Integer

Set @White = 1
Set @Green = 2
Set @Blue = 4
Set @Red = 8

Declare @Color Integer
Set @Color = 10

Select	Case When @Color & @White <> 0
             Then 'White,'
             Else ''
             End
        + Case When @Color & @Green <> 0
             Then 'Green,'
             Else ''
             End
        + Case When @Color & @Blue <> 0
             Then 'Blue,'
             Else ''
             End
        + Case When @Color & @Red <> 0
             Then 'Red,'
             Else ''
             End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hlybbi]

No this is not it, what i am looking for is some kind of mathmatic formula,

like i have these weekdays

Sunday = 1
Monday = 2
Tuesday = 4
Wednesday = 8
Thursday = 16
Friday = 32
Saturday = 64

and in my software i can choice sunday+tuesday and i get the value 5 be couse the value 4+1 is 5
Also i could choice wednesday+Friday+Saturday and i would get the value 104 be couse the value 8+32+64 is 104

what i am looking for is to be able to see in my stored procedure what the value 5 or 104 is

Best regards Hlynur

http://www.xodus.net

 

[SQLDenis]

create table days (dayname varchar(20), value int)
insert into days
select 'Sunday', 1 union all
select 'Monday' , 2 union all
select 'Tuesday' , 4 union all
select 'Wednesday' , 8 union all
select 'Thursday' , 16 union all
select 'Friday' , 32 union all
select 'Saturday' , 64

select sum(value) from days where dayname in('sunday','tuesday')
select sum(value) from days where dayname in('wednesday','Friday','Saturday' )

of course you can also stored the values passed in into a table and join the two tables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hlybbi]

Yes this is almoust it,
i cant figure out a way to rewerse this like if i have the number 5 and i need to find out witch days this number stands for

Best regards Hlynur

http://www.xodus.net

 

[gmmastros]

You can accomplish this with a user defined function.

Create Function dbo.GetWeekdayNames
	(
	@Value Integer
	)
Returns VarChar(100)
As
Begin
Declare @Sunday Integer
Declare @Monday Integer
Declare @Tuesday Integer
Declare @Wednesday Integer
Declare @Thursday Integer
Declare @Friday Integer
Declare @Saturday Integer

Set @Sunday = 1
Set @Monday = 2
Set @Tuesday = 4
Set @Wednesday = 8
Set @Thursday = 16
Set @Friday = 32
Set @Saturday = 64

Declare @Result VarChar(100)
Set @Result = ''

If @Value & @Sunday <> 0
	Set @Result = @Result + 'Sunday,'

If @Value & @Monday <> 0 
	Set @Result = @Result + 'Monday,'

If @Value & @Tuesday <> 0 
	Set @Result = @Result + 'Tuesday,'

If @Value & @Wednesday <> 0 
	Set @Result = @Result + 'Wednesday,'

If @Value & @Thursday <> 0 
	Set @Result = @Result + 'Thursday,'

If @Value & @Friday <> 0 
	Set @Result = @Result + 'Friday,'

If @Value & @Saturday <> 0 
	Set @Result = @Result + 'Saturday,'

If Right(@Result, 1) = ','
	Set @Result = Left(@Result, Len(@Result)-1)

Return @Result

End

Then, to use it...

Select dbo.GetWeekdayNames(104)
Select dbo.GetWeekdayNames(5)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hlybbi]

yes this is great thx a million.
this code saves my life

now i can complete my software

Best regards Hlynur

http://www.xodus.net