Solved

Stored Procedure

Posted on 2011-02-22
9
434 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:TCHGirl
9 Comments
 
LVL 3

Expert Comment

by:DFW_Ed
Comment Utility
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
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Exactly what are you trying to do.

You have to be specific about about your requirements
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
 

Author Comment

by:TCHGirl
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 3

Expert Comment

by:DFW_Ed
Comment Utility
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
 

Author Comment

by:TCHGirl
Comment Utility
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
 
LVL 3

Expert Comment

by:DFW_Ed
Comment Utility
so the bridge tab should show all info that has been input in the other forms?  
0
 

Author Comment

by:TCHGirl
Comment Utility
that is correct
0
 
LVL 3

Accepted Solution

by:
DFW_Ed earned 500 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now