troubleshooting Question

How do I convert code into a SQL stored procedure?

Avatar of Karen Wilson
Karen WilsonFlag for United States of America asked on
Visual Basic.NETMicrosoft SQL Server
9 Comments1 Solution855 ViewsLast Modified:
I recently asked the best method for me to speed up a process in an application.  The answer was to convert it to a stored procedure.  Let the server do the work, versus the application.  I use LINQ to SQL in a Visual Studio 2013 windows forms application.  

I am attaching the original code.

I never use stored procedures because the method I use works. So this is new to me.

First, I created a stored procedure and it works fine.
CREATE PROCEDURE [dbo].[sp_GetCrazyWeeks]
      @NASA_Contract nvarchar(50) = NULL,
      @Rev nvarchar(10) = NULL
      Set nocount on;
      SELECT *
      from tblCrazyLandDevel
      where NASA_Contract = @NASA_Contract and Rev = @Rev
      order by WeekEnding ASC

I used LINQ and added the SPROC to my dbml.  Works fine.

I call it up in my application.  Works fine.  

Dim crzyWk = d.sp_GetCrazyWeeks(CStr(Me.NASA_ContractComboBox.SelectedItem), CStr(Me.ComboBoxRev.SelectedItem))

What I do in the app is  "For Each" and "Next"  each row I retrieve.  I try it out in a simple thing and it works.

 For Each x In crzyWk
                Dim xTO As String = x.NASA_Contract
                Dim xRev As String = x.Rev
                Dim xWE As String = CStr(x.WeekEnding)

Next, inside this, I pull additional data from the server, as this is a compiling process and update many variables.

So I wrote some more SPROCs.  
Dim lwpPlans = d.sp_LastWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))
Dim thisWeekPlans = d.sp_ThisWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))

Now I am stuck because I don't know how to use was I am pulling.  It's there, I can see it, but what to do.  I usually do it like this, but something similar is not working.
 planLab = thisWeekPlans.Item(0).Labor

And I am also thinking, what is the point between the SPROC and what I am currently doing??  Seriously, it doesn't seem to be any faster.  So I must be approaching the SPROC-thing  wrong.  Should I be SPROC-ing exclusively in SQL Server and just passing parameters through the application?  

Any suggestions, direction, links would be appreciated.
Many thanks!
Karen Wilson

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros