[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Testing Enum values in Stored Procedures

rking

3/21/2007 6:57:00 PM

I'm writing several stored procedures that test the value of Integers which
are represented by enumerated values in the VB application. My procedures
are testing for 0,1,2 . . ., instead of new, scheduled, sent, cancelled, . . .

I've considered using a user defined function such as
uf_pmntStatus (status as varchar(10) as integer
set ud_pmntStatus = case status when 'new' then 0
when 'scheduled' then 1
when 'sent' then 2
.
.
.
end

Then I could use
item = uf_pmntStatus('new')

However, I'm now maintaining a list in my UDF and a list in my common VB area.
Is it reasonable, possibe, acceptable to write a CLR routine for SQL that
could use the VB defined enum values?
2 Answers

xyb

3/22/2007 1:51:00 AM

0

On 3?22?, ??2?57?, rking(ISV) <r...@nospam.nospam> wrote:
> I'm writing several stored procedures that test the value of Integers which
> are represented by enumerated values in the VB application. My procedures
> are testing for 0,1,2 . . ., instead of new, scheduled, sent, cancelled, .. . .
>
> I've considered using a user defined function such as
> uf_pmntStatus (status as varchar(10) as integer
> set ud_pmntStatus = case status when 'new' then 0
> when 'scheduled' then 1
> when 'sent' then 2
> .
> .
> .
> end
>
> Then I could use
> item = uf_pmntStatus('new')
>
> However, I'm now maintaining a list in my UDF and a list in my common VB area.
> Is it reasonable, possibe, acceptable to write a CLR routine for SQL that
> could use the VB defined enum values?

why not add a table of status,then you can reference it at everywhere
as you want to no matter at proc or functions :)

changliw

3/22/2007 6:09:00 AM

0

Hi,
I understand that you would like to know if it is reasonable, possible and
acceptable to write a CLR routine for SQL to use your VB defined enumerated
values since it is a big effort for you to maintain two copies of the list:
one is in your UDF and the other is in your VB application.
If I have misunderstood, please let me know.

I do not recommend that you write a CLR routine in your situation no matter
it is possible, since it need to access the enum values in your VB
applicationruntime. If your VB application does not work, any call to the
UDF will fail.

I agree with Xiangyuan. The suggestion is reasonable. It does make sense to
use a table to store the enum values.

Please feel free to let us know if you have any questions or concerns.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================