Stored Procedure

I am still pretty new at writing my own stored procedure.  I am creating and new program and I have to pull some data from different tables and I am not sure how of if this can be done.  The attached file shows a list of fields that are already populated in different tables.  Now what is being asked of me is to bring them together without having to combine these tables into one table.  I needs a store procedure that will let this happen, so that if I go to this tab these fields will already have the information in there and if it doesn't then we are able to update this information.  No sure if this make sense.
Store-Procedure.docx
TCHGirlAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DFW_EdConnect With a Mentor Commented:
you may want to consider using a view since you can modify information using a view, i would test results though to be sure the inserts and updates are happening as you expect.

as for a stored procedure, to display the info would be easy enough, to display and also perform inserts/updates to any changed fields is possible but i'd recommend having a separate proc for insert and one for update, but that's just a personal preference.  IFor a single proc use 'd look at using MERGE in the procedure for the insert/update portion:

http://msdn.microsoft.com/en-us/library/bb510625.aspx

the join structure below should be sufficient for collecting the data you need:

from ESL_Client eslc
left join Youth y on eslc.tchid = y.tchid
left join Cmjclient cmjc on cmjc.tchid = y.tchid
left join newEmployClient nec on nec.tchid = y.tchid
left join NewClientInfo nci on nci.tchid = y.tchid


0
 
DFW_EdCommented:
don't think i'm understanding the question fully.  Are you needing the procedure to just join the tables and produce the information you need?  like this:

select eslc.Edcm,
y.Ythcm,
cmjc.Cjcm,
nec.Betscm,
nec.Ritecm,
nec.Tagcm,
nec.Empcm,
nci.Bets1,
nci.Cj1,
nci.Youth1,
nci.Rite1,
nci.Tag1,
nci.Education1,
nci.Bridge1

from ESL_Client eslc
join Youth y on eslc.tchid = y.tchid
join Cmjclient cmjc on cmjc.tchid = y.tchid
join newEmployClient nec on nec.tchid = y.tchid
join NewClientInfo nci on nci.tchid = y.tchid

or do you need something based on parameters provided by a user?

could also make a view if this is something you're not going to use to update tables
0
 
Ephraim WangoyaCommented:
Exactly what are you trying to do.

You have to be specific about about your requirements
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
lcohanDatabase AnalystCommented:
You should be able to do that with a data grid - just check "Database operations in DatagridView" at the link below and one advice - if you work with stored procedures do not prefix them "sp_...." like Microsoft system objects.

http://vb.net-informations.com/datagridview/vb.net_datagridview_tutorial.htm


Other than that you could easily create a SQL View instead to sqlqct data from all the tables/columns joined by common "tchid" and use that in a data grid.
0
 
TCHGirlAuthor Commented:
dfw ed.  This is not based on parameters.

it based on what I hope to be a simple insert and update procedure.  See attached on how I would like to have this stored procedure work
Intake.docx
0
 
DFW_EdCommented:
so are you wanting the education case manager to appear regardless of the tab used and whether or not the check box for that additional tab is marked?  The attachment shows 7 check boxes from left-to-right "Bets" thru "Bridge" but only "Education" is marked.  Once marked with "Education" you want the case manager to show in the other tabs?  Does it currently show in the other tabes but not the "Bridge"?
0
 
TCHGirlAuthor Commented:
The two are not exclusive.  it shows on the first tab because that is where it is stored in the table.  So once I input the information onto the Intake tab, it has been entered there and in the table that holds the information.  Now when I open the bridge tab I want any data that shows on this screen to self populate with the information that  has already been entered somewhere else.  This is really hard to explain.

For example: I am in the education department and I enter all the education information in the intake tab. Somebody else entered other information into a youth program that has the same check boxes on their intake tab and has a different case manager field.  So when I open up my bridge tab any information that was entered on the youth  tab ie youth case manager, youth check box will show up on my bridge tab.  This way I don't have to go to the youth page to look this information up.

Does this help?
0
 
DFW_EdCommented:
so the bridge tab should show all info that has been input in the other forms?  
0
 
TCHGirlAuthor Commented:
that is correct
0
All Courses

From novice to tech pro — start learning today.