Here's the work done so far...

Here's the work done so far...

Post by Steve » Sat, 20 Dec 2003 04:48:29


Here is the actual routine I just completed.
Note that I get a cursor of ID's - from a Master_T table
I load a temp table with the "result set" of the Stored
Proc frmClaimEntry_View_Elig. The SP has extra arg's
that the VB client needs, but aren't required in this
arena. Then I load the Temp Table into a real table with
some additional columns.

I didn't want to do the "cursor" - I wanted to build
#EL_Tbl with set based logic...

Any suggestions?

Declare @MasId int
Declare @DOS datetime

Set @DOS='2003-11-30'

Set NoCount On

Declare MasIdList Cursor for
Select MasId From Master_T WHERE
MEMBERSSN<>'000000000'

Drop Table #EL_Tbl

Create Table #EL_Tbl (
ELMasId int
,ELGrp varchar(10)
,ELWho varchar(10)
,ELElig varchar(10))

Open MasIdList

Fetch Next From MasIdList into @MasId

While @@Fetch_Status=0
Begin
Print @MasId
Set @MasId=-@MasId
Insert #EL_Tbl Exec frmClaimEntry_View_Elig
0,0,'',0,0,'',0,'','',@MasId,@DOS,@DOS
Fetch Next From MasIdList into @MasId
End

Insert WelCard_T Select *,@DOS
,(SELECT TOP 1 Status From Entry_T ET
Left Join Master_T MA on MA.MasId=EL.ELMasId
Left Join Master_T MA2 on MA2.MemberSSN=MA.MemberSSN and
MA2.Affil=1
Where ET.MasID=MA2.MasID and ET.EntryDate<=@DOS
Order by EntryDate desc)
,null,null,getdate() from #EL_Tbl EL

Close MasIdList
Deallocate MasIdList

assist you with
procedure which
variable. Then you
stored procedure, but
side of the stored
variable in it.
help you out:
questions, please
help!
confers no rights.
 
 
 

Here's the work done so far...

Post by v-baiwe » Sat, 20 Dec 2003 18:15:27

Hi Steve,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

I have studied your code for some time but still confused because so many
information that are not clear in the code. I'd better come back to your
first post.

As you mentioned in your first post, you will define a stored procedure,
which will have a result set (someone is eligible for health benefits for a
particular date range), and you want to save it into a workable place.

In your first post, in the stored procedure you defined, you create a table
variable. However, you can create a table variable within a stored
procedure, but not pass it in as a parameter. The output parameters of
stored procedure include include either data (such as an integer or
character value) or a cursor variable (cursors are result sets that can be
retrieved one row at a time). Besides the cursor, you can also return data
as the following way to return multi-row result set. In you case, I suggest
you to save it
in a table. In your stored procedure, you could create a table, then save
the result to this table, then you could work on this table. When finished
using this table, you could drop it. The workload of programming will be
reduced.

You could refer to the 'Sharing Temp Table' part, especially the last
section of this part
http://www.yqcomputer.com/ ~sommar/share_data.html

Hope this could help your problem! If you still have questions, please feel
free to post new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

 
 
 

Here's the work done so far...

Post by Steve » Sun, 21 Dec 2003 02:09:59

k, here's a bit of an explanation.

We've spent a year developing a "general maintenance"
program in VB that allows us to create forms for a user
with no programming. These "generic" forms look
for "specially named" SP's that return grids of data that
get put into FLEX GRIDs and labels, etc. Sometimes the
SP's determine that errors from the user were done, so
they have a standard set of 9 parameters that control
things like form state, error message to print, etc.
That's what those first 9 parm's are. The other parm's
are named so that the "generic" program can bind labels
of data or grid columns on the form to the SP for the
call.

So the user gets to a column in the "claim entry" grid
and puts in the DOS - the grid knows to call this SP -
binds the MASID and the DOS data - calls the proc and
sometimes it gets an output parameter back ("DOS cannot
span the plan year", etc). These get flashed as msg box
and user forced to re-enter DOS. If the person is
eligible a "record set" comes back, that get's put into
columns in the grid to support the rest of the entry.

Now the whole point - I wanted to call the SP for every
person in the master file in query analyzer. If the SP
was a USER DEF function, you could embed it in the
SELECT ... From. It appears that a cursor is needed for
this requirement - since the SP needs to be called for
26000 id's from the MASTER_T table. If the SP returns a
recordset (meaning the person is eligible) it goes into
the temp table. If it returns some error message in the
OUTPUT parm's, I don't care, so the lack of RS is fine
here.

The reason I'm setting the MASTER ID to a negative value,
is to force the SP to return a consistent # of columns
when called in this fashion. Normally the columns
returned are based on conditions of the patient, age, etc.


assist you with
confused because so many
come back to your
stored procedure,
health benefits for a
workable place.
you create a table
within a stored
output parameters of
integer or
result sets that can be
can also return data
you case, I suggest
table, then save
table. When finished
programming will be
especially the last
questions, please feel
confers no rights.
 
 
 

Here's the work done so far...

Post by v-baiwe » Tue, 23 Dec 2003 15:11:47

Hi Steve,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

How about my suggestion in the last post? When a stored is executed with
some input parameters, it save the result set according to your cretirion
into an empty permanent table. Because the stored procedure cannot pass the
table parameters, and you want to avoid using cursor. You can empty this
table at as the first step of your stored procedure. Your stored procedure
may have some output parameters for your client to show message of show the
record in the table storing result set in your 'FLEX GRIDs'.

I hope this is helpful for you to solve your problem. If you still have
question, please feel free to post message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
 
 
 

Here's the work done so far...

Post by Steve » Tue, 23 Dec 2003 23:41:31

Thank you for your information and assistance...

We have so much "business logic" in stored proc's -
designed primarily for use from the CLIENT VB program,
making them very specific to being called for a "single"
record or situation.

We should have seen the "need" for this routine to be
called in a variety of ways and made it a USER DEFINED
FUNCTION. We could have then wrapped the UDF in a STORED
PROC for the need of the CLIENT VB program and still had
a way to call it in a SELECT * from ...

Some of the SP's are extremely simple - verifying that
columns in the FLEX GRID contain valid data against the
patient, etc. They would never (never say never) be
needed from outside the VB CLIENT.

This particular logic should have been "foreseen" to be
needed outside the VB CLIENT. As a development shop, we
have a general rule of not duplicating logic in two
places, so we will have to re-develop this logic into a
UDF (it's been in production now for 9 months) and wrap
that in a SP for the CLIENT.

assist you with
is executed with
to your cretirion
procedure cannot pass the
You can empty this
Your stored procedure
message of show the
GRIDs'.
you still have
am ready to help!
confers no rights.
 
 
 

Here's the work done so far...

Post by v-baiwe » Wed, 24 Dec 2003 20:39:24

Hi Steve,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

Well, thank you for the information you provided. Actually, I understand
that it is really a complicated process to do any change that will impact
the other part in a project. But if it is necessary, there may be not a
good solution. Actually, since you want to wrapped the UDF in a SP, as I
said before, it is impossible to use the statement like 'select * from exec
sp'. Beside the cursor, please try this way:

select * from openquery(Server_Alias, 'exec your_sp') in your client side.
If you want to have parameters, please see the example below.

As I mentioned for using a table to save the results set and the concern of
wrap a UDF in a SP and return a result set, I did some test as follows:

use pubs
go
CREATE FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storid AND t.title_id = s.title_id)
---this User Defined Function will return a table, or we can say a result
set
go

create proc UDF_in_SP
@a char(4)
as
select * from salesbystore(@a)
--this is the function in a sp, the sp has parameters for the function
--or for some else purpose
go

create table result_set
(title varchar(80),
qty smallint
)
go

insert result_set exec UDF_in_SP '8042'
--here, you could use a parameter for the SP and saved
--the result to a workable place

select * from result_set

drop function salesbystore
drop proc udf_in_sp
drop table result_set

You can seperate the 'select from ...' into to steps. First: run your SPs,
then select from a table that return the result. and the SP could return
the table name as a parameter for the next step if you like to do this.

So, for the client side, you should do some modification to get the result
from a SP include your UDF.

Then is one thing I should point out that, you must only have one 'select'
statment in that, because you cannot insert into a table from two results
sets.

If any more problems, please feel free to post new message here and I am
ready to help!

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.